Перевести sql базу в однопользовательский режим: перевод БД SQL сервер в монопольный режим доступа

восстановление системных баз данных в отказоустойчивом кластере — Блог

null

восстановление системных баз данных в отказоустойчивом кластере

15 января 2018

0

Maxim Zhukov

Введение

Не все сопровождающие MS SQL администраторы задумываются над тем, что нужно заранее быть готовым к сценарию восстановления системной базы данных, в частности MASTER, особенно в окружении Microsoft FailoverCluster.

Постановка проблемы

Основной проблемой (особенностью) восстановления MASTER будет являться перевод базы данных в однопользовательский режим(single user mode).

В обычном single окружении нужно добавить ключ -m в параметрах запуска в оснастке Диспетчер конфигурации SQL Server

В кластерной среде имеются LooksAlive и IsAlive процессы, которые могут занять single сессию в перезапустившемся сервере. 

Действия

По этой причине план действий будет начинаться с остановки кластера

1. Остановить кластер через оснастку Failover Cluster Manager — Stop Roles
2. на узле кластера, где остались активными Cluster Core ресурсы (storage и cluster-ip) в диспетчере конфигурации SQL Server установить параметр запуска -m для запуска в однопользовательском режиме (single user mode) согласно документации.
3. Перезапустить сервис SQL Server — в указанном примере на скриншотах «SQL Server (MSSQLSERVER)«
4. Подключиться к перезапущенному экземпляру SQL Server, который уже находится в Single User Mode 
5. Выполнние (например в sqlcmd) восстановления Master DB, согласно документации.

1> RESTORE DATABASE master FROM DISK = 'Z:\SQLServerBackups\master.bak' WITH REPLACE;  
2> GO  

Завершающие действия

1. После восстановления остановить SQL Server в остнастке SQL Server Configuration Manager. Рекомендую  проверить параметры запуска сервиса.
2. Запустить  кластер через оснастку Failover Cluster Manager — Start Roles

P.S.

Имея план восстановления своей инфраструктуры, а так же практику периодической апробации (в том числе и на время выполнения в тестовой среде) качество обслуживание и сокращение времени простоя в случае ЧП сокращается в разы. Так же не нужно нивелировать положительное влияние на рост и укрепление компетенции задействованных в этих «учениях» ИТ-специалистов.

А кто-то из угла на это смотрит и  ̶о̶б̶и̶ж̶е̶н̶н̶о̶ презрительно фыркает «Бюрррократия…Бюрррократия….«

Назад

Вперед

Please enable JavaScript to view the comments powered by
Disqus.

Postgres Pro Enterprise : Документация: 15: postgres : Компания Postgres Professional

RU

EN

RU EN

postgres

postgres — Сервер баз данных Postgres Pro

Синтаксис

postgres [параметр…]

Описание

postgres это сервер баз данных Postgres Pro. Для получения доступа к базе данных клиент устанавливает соединение (локально или по сети) с сервером postgres. После установки соединения сервер postgres поднимает выделенный процесс для его обслуживания.

Один экземпляр postgres всегда управляет данными ровно одного кластера баз данных. Кластер — это коллекция баз данных, хранящихся в файловой системе в определённом размещении («области данных»). На одном физическом сервере можно запустить несколько экземпляров postgres одновременно, при условии, что они используют различные области данных и порты. При запуске postgres необходимо указать размещение данных, которое задаётся в параметре -D или переменной окружения PGDATA, значение по умолчанию отсутствует. Обычно -D или PGDATA указывает на каталог, созданный во время развёртывания кластера с помощью initdb. Иные варианты рассмотрены в Разделе 19.2.

По умолчанию postgres запускается не в фоновом режиме, а вывод журнала осуществляет в стандартный поток ошибок. На практике postgres должен запускаться в фоновом режиме, возможно, при старте системы.

Команду postgres также возможно использовать в однопользовательском режиме. В основном этот режим используется на этапе инициализации при выполнении initdb. Иногда он также применяется в целях отладки или после аварийного сбоя. Заметьте, что однопользовательский режим не вполне подходит для отладки сервера ввиду отсутствия в нём межпроцессного взаимодействия и блокировок. Когда сервер запускается в однопользовательском режиме из командной строки, он может принимать запросы и выводить их результаты на экран, но формат этого вывода ориентирован больше на разработчиков, чем на обычных пользователей. В этом режиме текущим пользователем считается пользователь под номером 1, который неявно наделяется правами суперпользователя. При этом данный пользователь может фактически не существовать, поэтому в ряде случаев этот режим даёт возможность вручную восстановить базу при повреждении системных каталогов.

Параметры

Программа postgres принимает следующие аргументы командной строки. За подробным описанием параметров обратитесь к Главе 19. От необходимости вводить большинство этих параметров можно избавиться, записав их в файл конфигурации. Некоторые (безопасные) параметры можно также задать со стороны подключающегося клиента (в зависимости от приложения), чтобы они применялись только к одному сеансу. Например, если установлена переменная окружения PGOPTIONS, клиенты на базе libpq передадут эту строку серверу, который воспримет её как параметры, передаваемые в командной строке postgres.

Параметры общего назначения

-B количество-буферов

Устанавливает количество разделяемых между процессами буферов. Значение по умолчанию выбирается автоматически при развёртывании кластера с помощью initdb. Установка флага аналогична конфигурации параметра shared_buffers.

-c имя=значение

Устанавливает заданный параметр времени выполнения. Конфигурационные параметры, поддерживаемые Postgres Pro, описаны в Главе 19. Большинство других параметров командной строки на самом деле представляют собой краткие формы такого присваивания значений параметрам. Для установления нескольких параметров -c можно указывать многократно.

-C имя

Отображает значение заданного параметра времени выполнения и завершается. (См. также описание параметра -c выше.) При этом выдаются значения из postgresql.conf с учётом возможных переопределений параметров при вызове. Однако параметры, установленные при запущенном сервере, в выводе не отражаются.

Для большинства параметров можно запросить значения при работающем сервере. Однако для некоторых параметров, вычисляемых во время выполнения, сервер должен быть отключён (например, shared_memory_size, shared_memory_size_in_huge_pages и wal_segment_size).

Параметр предназначен для приложений, взаимодействующих с сервером, например pg_ctl, и запрашивающих параметры конфигурации. Пользовательские приложения должны использовать команду SHOW или представление pg_settings.

-d уровень-отладки

Устанавливает уровень отладки (от 1 до 5). Чем выше значение, тем подробнее осуществляется вывод в журнал сервера. Также возможно передать -d 0 для отдельного сеанса, что предотвратит в его рамках влияние выставленного для postgres значения.

-D datadir

Указывает размещение конфигурационных файлов базы в пределах файловой системы. За подробностями обратитесь к Разделу 19.2.

-e

Устанавливает формат вводимых дат по умолчанию в «European» с последовательностью значений DMY. Также влияет на вывод дня, идущего перед значением месяца, более подробно см. Раздел 8.5.

-F

Отключает вызовы fsync для увеличения производительности, но с увеличением рисков потери данных в случае краха системы. Этот параметр работает аналогично параметру конфигурации fsync. Внимательно прочтите документацию перед использованием данного параметра!

-h компьютер

Указывает IP-адрес или имя компьютера, на котором сервер postgres принимает клиентские подключения по TCP/IP. Значением может быть список адресов, разделённых запятыми, либо символ *, обозначающий все доступные интерфейсы. Если значение опущено, то подключения принимаются только через Unix-сокеты. По умолчанию принимаются подключения только к localhost. Флаг работает аналогично конфигурационному параметру listen_addresses.

-i

Позволяет клиентам подключаться по TCP/IP. Без этого параметра допускаются лишь локальные подключения. Действие этого параметра аналогично действию параметра конфигурации listen_addresses со значением * в postgresql.conf или ключа -h.

Параметр устарел, так как не даёт полной функциональности listen_addresses. Лучше устанавливать значение listen_addresses напрямую.

-k каталог

Указывает каталог Unix-сокета, через который postgres будет принимать подключения. Значением параметра может быть список каталогов через запятую. Если это значение пустое, использование Unix-сокетов запрещается, разрешаются только подключения по TCP/IP. По умолчанию выбирается каталог /tmp, но его можно сменить на этапе компиляции. Этот параметр действует аналогично параметру конфигурации unix_socket_directories.

-l

Включает поддержку безопасных соединений с использованием SSL шифрования. Postgres Pro необходимо скомпилировать с поддержкой SSL для использования этого флага. Подробнее использование SSL описано в Раздел 18.9.

-N максимальное количество соединений

Устанавливает максимально возможное количество одновременных клиентских соединений. Значение по умолчанию устанавливается автоматически на этапе развёртывания с помощью initdb. Флаг работает аналогично конфигурационному параметру max_connections.

-p порт

Указывает порт TCP/IP или расширение файла локального Unix-сокета, через который postgres принимает подключения клиентских приложений. По умолчанию принимает значение переменной окружения PGPORT, или, если значение PGPORT не установлено, то используется значение, установленное на этапе компиляции (обычно это 5432). Если значение порта меняется, то на стороне клиентов это необходимо учитывать, установив, либо PGPORT, либо флаг командной строки.

-s

Отображает информацию о времени и другую статистику после каждой выполненной команды, что полезно для оценки производительности во время настройки количества буферов.

-S рабочая-память

Указывает базовый объём памяти, который сервер будет использовать для сортировок и хеш-таблиц, прежде чем прибегнуть к использованию временных файлов на диске. Обратитесь к описанию параметра work_mem, приведённому в Подразделе 19.4.1.

-V
--version

Отображает версию postgres и прерывает дальнейшее выполнение.

--имя=значение

Устанавливает заданный параметр времени выполнения. Является короткой формой ключа -c.

--describe-config

Выводит значения конфигурационных переменных сервера, их описаний и значений по умолчанию в формате команды COPY со знаком табуляции в качестве разделителя. В основном это предназначено для средств администрирования.

-?
--help

Выводит помощь по аргументам команды postgres.

Параметры для внутреннего использования

Далее описанные параметры, в основном, применяются в целях отладки, а в некоторых случаях при восстановлении сильно повреждённых баз данных. Их описание приведено для системных разработчиков Postgres Pro, поэтому они могут быть изменены без уведомления.

-f { s | i | o | b | t | n | m | h }

Запрещает использование специфических методов сканирования и объединения: s и i выключают последовательное сканирование и по индексу соответственно, а o, b и t выключает сканирование только по индексу, сканирование по битовым векторам, и сканирование по ID кортежей соответственно, в то время как n, m и h выключает вложенные циклы, слияния и хеширование соответственно.

Ни последовательное сканирование, ни вложенные циклы невозможно выключить полностью. Флаги -fs и -fn просто указывают планировщику избегать выполнения этих операций при наличии других альтернатив.

-n

Параметр предназначен для отладки сервера в случае аномального завершения процесса. Обычная практика в таком случае — завершение порождённых процессов с дальнейшей инициализацией разделяемой памяти и семафоров. Это связано с тем, что потерянный процесс мог повредить область разделяемой памяти. Параметр указывает postgres не производить повторной инициализации общих структур данных, что позволяет произвести дальнейшую отладку текущего состояния памяти и семафоров.

-O

Разрешает модифицировать структуру системных таблиц. Используется командой initdb.

-P

Игнорировать системные индексы при чтении, но продолжать обновлять их при изменениях системных таблиц. Это используется при их восстановлении после повреждения.

-t pa[rser] | pl[anner] | e[xecutor]

Выводит статистику по времени исполнения каждого запроса в контексте каждого системного модуля. Использование флага совместно с -s невозможно.

-T

Параметр предназначен для отладки сервера в случае аномального завершения процесса. Обычная практика в таком случае — завершение порождённых процессов с дальнейшей инициализацией разделяемой памяти и семафоров. Это связано с тем, что потерянный процесс мог повредить область разделяемой памяти. Параметр указывает postgres на необходимость остановки порождённых процессов сигналом SIGSTOP, но не завершит их, что позволяет разработчикам сделать снимки памяти процессов.

-v протокол

Указывает для данного сеанса версию протокола взаимодействия сервера с клиентом. Флаг используется лишь для внутренних целей.

-W секунды

При старте сервера производится задержка на указанное количество секунд, после чего производится процедура аутентификации, что позволяет подключить отладчик к процессу.

-Z

Проверяет, совместим ли текущий исполняемый файл postgres с определённым кластером. Если тип архитектуры или другие параметры компиляции, влияющие на совместимость кластера, не совпадают, программа возвращает код завершения 1 и выдаёт сообщение об ошибке, описывающее первую обнаруженную несовместимость. Иначе она завершается с кодом успешного результата, при этом не запуская кластер.

Для проведения этой проверки необходимо передать путь к каталогу данных целевого кластера.

Эта проверка будет полноценной, только если кластер и исполняемый файл postgres рассчитаны на одну архитектуру процессора и одинаковый порядок байтов.

Подсказка

В случае выявления несовместимости запустите pgpro_controldata с ключом -P или -C, чтобы получить список всех параметров, от которых зависит совместимость.

Параметры для однопользовательского режима

Следующие параметры применимы только для однопользовательского режима (см. раздел Однопользовательский режим ниже).

--single

Устанавливает однопользовательский режим. Параметр должен идти первым в командной строке.

база_данных

Указывает имя базы данных, к которой производится подключение. Параметр должен идти последним в командной строке. Если не указан, то используется имя текущего системного пользователя.

-E

Выводить все команды в устройство стандартного вывода прежде чем выполнять их.

-j

Считать признаком окончания ввода команды точку с запятой с двумя переводами строки, а не один перевод строки.

-r имя_файла

Отправляет вывод журнала сервера в файл filename. Этот параметр применяется лишь при запуске из командной строки.

Переменные окружения

PGCLIENTENCODING

Кодировка, используемая клиентом по умолчанию. Может переопределяться на стороне клиента, а также устанавливаться в конфигурационном файле сервера.

PGDATA

Каталог размещения данных кластера по умолчанию

PGDATESTYLE

Значение по умолчанию для параметра времени выполнения DateStyle. Применение этой переменной является устаревшим.

PGPORT

Порт по умолчанию, лучше устанавливать в конфигурационном файле.

Диагностика

Ошибки с упоминанием о semget или shmget говорят о возможной необходимости проведения более оптимального конфигурирования ядра. Подробнее это обсуждается в Разделе 18.4. Отложить переконфигурирование можно, уменьшив shared_buffers для снижения общего потребления разделяемой памяти Postgres Pro и/или уменьшив max_connections для снижения затрат на использование семафоров.

Необходимо внимательно проверять сообщения об ошибке с упоминанием о другом запущенном экземпляре, например, с использованием команды

$ ps ax | grep postgres

или

$ ps -ef | grep postgres

в зависимости от ОС. Если есть полная уверенность, что противоречий нет, необходимо самостоятельно удалить упомянутый в сообщении запирающий файл и повторить попытку.

Упоминание о невозможности привязки к порту в сообщениях об ошибках может указывать на то, что он уже занят другим процессом помимо Postgres Pro. Также сообщение может возникнуть при мгновенном рестарте postgres на том же порту. В этом случае нужно немного подождать, пока ОС не закроет порт, и повторить попытку. Ещё возможна ситуация, в которой используется резервный системный порт. Например, многие Unix-подобные ОС резервируют «доверительные» порты от 1024 и ниже, и лишь суперпользователь имеет к ним доступ.

Замечания

Для комфортного запуска и остановки сервера можно использовать утилиту pg_ctl.

Если возможно, не используйте сигнал SIGKILL для головного процесса postgres. В этом случае postgres не освободит системные ресурсы, например, разделяемую память и семафоры. Это может привести к проблемам при повторном запуске postgres.

Для корректного завершения postgres используются сигналы SIGTERM, SIGINT или SIGQUIT. При первом будут ожидаться все дочерние процессы до их завершения, второй приведёт к принудительному закрытию соединений, а третий — к незамедлительному выходу без корректного завершения, приводящему к необходимости выполнения процедуры восстановления на следующем старте.

Получая сигнал SIGHUP, сервер перечитывает свои файлы конфигурации. Также возможно отправить SIGHUP отдельному процессу, но это чаще всего бессмысленно.

Для отмены исполняющегося запроса, отправьте SIGINT обслуживающему его процессу. Для чистого завершения серверного процесса отправьте ему SIGTERM. Также см. pg_cancel_backend и pg_terminate_backend в Подразделе 9.27.2, которые являются аналогами в форме SQL-инструкций.

Сервер postgres обрабатывает SIGQUIT для завершения дочерних процессов в грязную, и сигнал не должен отправляться пользователем. Также не стоит посылать SIGKILL серверному процессу — головной postgres процесс расценит это как аварию и принудительно завершит остальные порождённые, как это было бы сделано при процедуре восстановления после сбоя.

Ошибки

Флаги, начинающиеся с -- не работают в ОС FreeBSD или OpenBSD. Чтобы обойти это, используйте -c. Это ошибка ОС. В будущих релизах Postgres Pro будет предоставлен обходной путь, если ошибка так и не будет устранена.

Однопользовательский режим

Для запуска сервера в однопользовательском режиме используется, например, команда

postgres --single -D /usr/local/pgsql/data другие параметры my_database

Необходимо указать корректный путь к каталогу хранения данных в параметре -D, или установить переменную окружения PGDATA. Также замените имя базы данных на необходимое.

Обычно перевод строки в однопользовательском режиме сервер воспринимает как завершение ввода команды; точка с запятой не имеет для него такого значения, как для psql. Поэтому, чтобы ввести команду, занимающую несколько строк, необходимо добавлять в конце каждой строки, кроме последней, обратную косую черту. Обратная косая черта и следующий за ней символ перевода строки автоматически убираются из вводимой команды. Заметьте, что это происходит даже внутри строковой константы или комментария.

Но если применить аргумент командной строки -j, одиночный символ перевода строки не будет завершать ввод команды; это будет делать последовательность «точка с запятой, перевод строки, перевод строки». То есть для завершения команды нужно ввести точку с запятой, и сразу за ней пустую строку. Просто точка с запятой, дополненная переводом строки, в этом режиме не имеет специального значения. Внутри строковых констант и комментариев такая завершающая последовательность воспринимается в том же ключе.

Вне зависимости от режима ввода, символ точки с запятой, введённый не прямо перед или в составе последовательности завершения команды, воспринимается как разделитель команд. После ввода завершающей последовательности введённые с разделителями несколько операторов будут выполняться в одной транзакции.

Для завершения сеанса введите символ конца файла (EOF, обычно это сочетание Control+D). Если вы вводили текст после окончания ввода последней команды, символ EOF будет воспринят как символ завершения команды, и для выхода потребуется ещё один EOF.

Заметьте, что однопользовательский режим не предоставляет особых возможностей для редактирования команд (например, нет истории команд). Также в однопользовательском режиме не производятся никакие фоновые действия, например, не выполняются автоматические контрольные точки или репликация.

Примеры

Для запуска postgres в фоновом режиме с параметрами по умолчанию:

$ nohup postgres >logfile 2>&1 </dev/null &

Для запуска postgres с определённым портом, например, 1234:

$ postgres -p 1234

Для соединения с помощью psql укажите этот порт в параметре -p:

$ psql -p 1234

или в переменной окружения PGPORT:

$ export PGPORT=1234
$ psql

Именованный параметр времени выполнения можно указать одним из приведённых способом:

$ postgres -c work_mem=1234
$ postgres --work-mem=1234

Любой из методов переопределяет значение work_mem конфигурации postgresql. conf. Символ подчёркивания в именах можно указать и в виде тире. Задавать параметры обычно (не считая кратковременных экспериментов) лучше в postgresql.conf, а не в аргументах командной строки.

См. также

initdb, pg_ctl

Оператор ALTER DATABASE SET SINGLE_USER в SQL Server

В этой статье мы рассмотрим назначение инструкции ALTER DATABASE SET SINGLE_USER. ИЗМЕНИТЬ
DATABASE SET SINGLE_USER используется для перевода базы данных в однопользовательский режим. Когда какая-либо база данных находится в однопользовательском режиме,
новый пользователь не может подключиться к базе данных. Однако пользователи, которые уже подключены к базе данных, не отключаются.

Когда вы перезаписываете базу данных с помощью резервной копии или пытаетесь отсоединить какую-либо базу данных, вы можете изменить режим доступа пользователей на SINGLE_USER, чтобы гарантировать, что новые пользователи не смогут подключиться к базе данных. Когда вы запускаете ALTER
DATABASE SET SINGLE_USER не завершается, поскольку пользователи все еще подключены к нему.

Чтобы переопределить это поведение, мы можем использовать любой из следующих вариантов завершения.

1. С НЕМЕДЛЕННЫМ ОТКАТОМ: Эта опция отменяет все открытые транзакции. Эта опция не ожидает завершения транзакции. Когда вы откатываете длительную транзакцию, выполнение команды занимает больше времени, поэтому убедитесь, что вы проверили все длительные транзакции перед выполнением команды.
2. БЕЗ ОЖИДАНИЯ: Эта опция не откатывает ни одну транзакцию, но ожидает завершения всех транзакций. Он ждет определенное время, и если транзакция не завершается, оператор ALTER DATABASE завершается ошибкой. Этот подход считается самым чистым методом изменения режима доступа, поскольку он не откатывает ни одну транзакцию.

Разрешение

Для выполнения оператора ALTER DATABASE SET SINGLE_USER требуется разрешение ALTER DATABASE.

Различные методы доступа в режиме базы данных для SINGLE_USER

Мы меняем режим доступа к базе данных на одного пользователя, используя любой из следующих методов:

1. Изменение свойства базы данных в SQL Server Management Studio (SSMS)
2. Оператор T-SQL ALTER DATABASE

Изменить свойство базы данных в SQL Server Management Studio (SSMS)

Чтобы изменить режим базы данных с помощью SSMS, откройте SQL Server Management Studio Подключиться к ядру базы данных Развернуть Базы данных Щелкните правой кнопкой мыши AdventureWorks2017 .

В диалоговом окне свойств базы данных нажмите Options . Щелкните раскрывающийся список «Ограничить доступ» и
выберите ОДИН_ПОЛЬЗОВАТЕЛЬ. Нажмите OK, чтобы сохранить конфигурацию.

Вы получите сообщение о том, что мы должны закрыть все открытые соединения перед изменением режима доступа . Нажмите OK, чтобы закрыть все активные пользовательские соединения и изменить режим доступа.

Режим доступа к базе данных AdventureWorks2017 изменен на SINGLE_USER . Вы можете увидеть
(Single-User) указывается с именем базы данных в студии управления SQL Server.

Оператор T-SQL ALTER DATABASE

Мы можем использовать оператор ALTER DATABASE SET SINGLE_USER, чтобы изменить режим доступа к пользовательской базе данных на SINGLE_USER.
Чтобы изменить режим доступа к файлу AdventureWorks2017. Перед сменой режима доступа к БД мы должны закрыть
открытые соединения. Для этого мы должны использовать условие завершения ROLLBACK IMMEDIATE .
Команда ALTER DATABASE должна быть записана следующим образом:

Вы получите сообщение ниже.

Неквалифицированные транзакции откатываются. Расчетное завершение отката: 0%.

Неквалифицированные транзакции откатываются. Расчетное завершение отката: 100%.

Режим доступа к базе данных AdventureWorks2017 будет изменен на ОДИН ПОЛЬЗОВАТЕЛЬ . Для проверки выполните следующий запрос:

Применение

Теперь давайте рассмотрим назначение и использование инструкции ALTER DATABASE SET SINGLE_USER .

1. Отсоединить базу данных
2. ВОССТАНОВИТЬ резервную копию существующей базы данных

Отсоединить базу данных

Когда мы отсоединяем базу данных пользователей, мы должны закрыть все открытые соединения. Перед отсоединением базы данных в производственной среде администраторы баз данных отправляют электронное письмо, чтобы уведомить пользователей о необходимости отключения от приложения. Иногда к базам данных также подключается часть приложения или модуль приложения. В таких случаях команда отсоединения завершается ошибкой, поскольку приложение подключено к базе данных. В этом случае мы должны принудительно закрыть соединение и установить режим доступа SINGLE_USER для отключения базы данных. Для этого мы должны использовать предложение WITH ROLLBACK IMMEDIATE , чтобы принудительно отключить всех пользователей и открыть соединения. Команда ALTER DATABASE записывается следующим образом:

Приведенная выше команда закроет все открытые соединения и установит базу данных в ОДИН ПОЛЬЗОВАТЕЛЬ . Запустите
следующий запрос для отключения базы данных.

База данных будет успешно отключена.

ВОССТАНОВЛЕНИЕ резервной копии существующей базы данных

Нам, как администраторам баз данных, необходимо обновить базы данных разработки или тестирования. Мы можем обновить процесс базы данных
с помощью метода отсоединения и подключения или восстановления резервной копии существующей базы данных путем ее перезаписи. При перезаписи
существующей базе данных, мы можем столкнуться с ошибкой: Для демонстрации я перезаписываю AdventureWorks2017
базу данных, используя резервную копию, которую я сделал. Чтобы восстановить резервную копию, выполните следующий запрос:

Вы можете столкнуться со следующей ошибкой:

Сообщение 3101, уровень 16, состояние 1, строка 3

Эксклюзивный доступ не может быть получен, поскольку база данных используется.

Сообщение 3013, уровень 16, состояние 1, строка 3

RESTORE DATABASE аварийно завершает работу.

Чтобы исправить эту ошибку, мы должны закрыть все открытые соединения с базой данных AdventureWorks2017 и изменить
режим доступа к базе данных SINGLE_USER . Для этого выполните приведенный ниже запрос.

База данных находится в режиме SINGLE_USER , попробуйте восстановить резервную копию, выполнив следующую команду:

Чтобы убедиться, что резервная копия успешно восстановлена, выполните следующий запрос.

Как видите, база данных успешно восстановлена.

• Примечание: Перед восстановлением базы данных обязательно закройте соединение, которое используется для изменения
  режим доступа к базе данных

Резюме

В этой статье мы узнали об операторе ALTER DATABASE SET SINGLE_USER . Мы изучили использование оператора и необходимое разрешение для его выполнения. Кроме того, я продемонстрировал различные способы установки режима доступа к базе данных SINGLE_USER и условия завершения.

• Автор
• Последние сообщения

Нисарг Упадхьяй

Нисарг Упадхьяй — администратор базы данных SQL Server и сертифицированный специалист Microsoft, имеющий более 8 лет опыта администрирования SQL Server и 2 года администрирования базы данных Oracle 10g.

Он имеет опыт проектирования баз данных, настройки производительности, резервного копирования и восстановления, настройки высокой доступности и аварийного восстановления, миграции и обновлений баз данных. Он получил степень бакалавра технических наук в Университете Ганпат. С ним можно связаться по адресу nisargupadhyay87@outlook.com

Последние сообщения Нисарга Упадхьяя (см. все)

Как изменить базу данных SQL Server в однопользовательском и многопользовательском режимах

Администраторы базы данных SQL Server могут использовать SQL Server Enterprise Manager для SQL Server 2000, чтобы установить для базы данных однопользовательский или многопользовательский режим.
Точно так же SQL Server Management Studio можно использовать для SQL Server 2005 для изменения базы данных в однопользовательский или многопользовательский режим.
Также вы можете изменить режим доступа к базе данных с помощью команд sql, таких как: ALTER DATABASE и sp_dboption .

ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

или

EXEC sp_dboption ‘Работает’, ‘один пользователь’, ‘ложь’

EXEC sp_dboption ‘Работает’, ‘один пользователь’, ‘истина’

Чтобы изменить состояние базы данных, чтобы ограничить доступ к однопользовательскому режиму, сначала откройте Microsoft SQL Server Management Studio и подключитесь к экземпляру SQL Server.
Откройте список доступных баз данных, принадлежащих связанному экземпляру, в окне обозревателя объектов.
Щелкните правой кнопкой мыши базу данных SQL Server, режим которой вы хотите установить для одного пользователя, и выберите «Свойства» в контекстном меню.

Когда вы щелкаете пункт меню свойств, для выбранной базы данных отображается следующий экран «Свойства базы данных».
Я использовал базу данных Works для образцов, использованных в статье.

Выберите страницу «Параметры» из списка в левой части экрана.
Если вы прокрутите список параметров для параметров состояния, вы увидите параметры базы данных «Ограничение доступа» с тремя параметрами, перечисленными в поле со списком.

Режимы ограничения доступа: Множественные, одиночные и ограниченные режимы .

Если вы выберете Одиночный режим и нажмете кнопку OK, вы сможете либо успешно изменить режим доступа к базе данных на одного пользователя, либо вам не удастся изменить режим доступа из-за наличия активных открытых соединений с базой данных Works.
Среда Management Studio IDE предложит вам закрыть все другие подключения к соответствующей базе данных для успешного процесса изменения параметров базы данных.

Чтобы изменить свойства базы данных, SQL Server должен закрыть все остальные соединения с базой данных. Вы уверены, что хотите изменить свойства и закрыть все остальные соединения?

После успешного выполнения команды alter база данных Works будет отображаться, как показано в окне Object Explorer.

Если существует активное соединение, отличное от Management Studio, и вы нажмете Works базы данных в SQL Server Management Studio, появится следующее предупреждающее сообщение:

База данных Works недоступна. (ObjectExplorer)

Если щелкнуть правой кнопкой мыши базу данных Works, появится следующее сообщение об ошибке.

База данных Works уже открыта и может иметь только одного пользователя одновременно. (Microsoft SQL Server, ошибка: 924)

После изменения базы данных в однопользовательском режиме ее можно снова изменить обратно в многопользовательский режим, выполнив приведенную ниже команду sql.

ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

Но если эта команда запущена из соединения, которое не является единственным активным соединением с соответствующей базой данных, команда завершится ошибкой со следующим сообщением:

Сообщение 5064, уровень 16, состояние 1, строка 1
В настоящее время невозможно изменить состояние или параметры базы данных «Работает».
База данных находится в однопользовательском режиме, и в данный момент к ней подключен пользователь.
Сообщение 5069, уровень 16, состояние 1, строка 1
Оператор ALTER DATABASE не выполнен.

В случае, если вы хотите изменить режим доступа к базе данных с однопользовательского режима на многопользовательский или с многопользовательского режима на однопользовательский режим, вам, возможно, придется закрыть все активные открытые соединения с базой данных.

В таком случае вам нужно найти все открытые соединения и выполнить команду « Kill @spid », чтобы закрыть все соединения.

Для получения списка открытых подключений к конкретной базе данных вы можете запустить аналогичную команду, как показано ниже:

выберите spid из master..sysprocesses, где dbid = db_id(‘Works’) и spid <> @@spid

Второй частью задачи будет закрытие открытых соединений с помощью команды Kill.
Этого можно добиться, запустив команды уничтожения внутри курсора или создав динамическую команду sql, которая уничтожает активные подключения к соответствующей базе данных.

Давайте закодируем закрытие открытых соединений с помощью курсора T-SQL:


DECLARE @DatabaseName nvarchar(50)
DECLARE @SPId int

SET @DatabaseName = N'Works'

DECLARE my_cursor CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName ) AND SPId <> @@SPId

OPEN my_cursor

FETCH NEXT FROM my_cursor INTO @SPId

WHILE @@FETCH_STATUS = 0
BEGIN
KILL @SPId

FETCH NEXT FROM my_cursor INTO @SPId
END

CLOSE my_cursor
DEALLOCATE my_cursor

После закрытия всех соединений, кроме текущего процесса (@@spid)
если мы пытаемся изменить режим доступа к базе данных, в которой мы находимся из
из многопользовательского режима в однопользовательский, теперь мы можем запустить команду ALTER DATABASE
для однопользовательского режима или многопользовательского режима.

ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT

или

ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT

Вы также можете использовать следующие sp_dboption команды sql для изменения
вариант базы данных для одного пользователя.