Как получить эксклюзивный доступ к базе данных SQL Server 2005 для восстановления?

Всякий раз, когда я восстанавливаю резервную копию моей базы данных в SQL Server, я получаю следующую ошибку:

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Обычно, чтобы обойти это, я просто перезапускаю сервер. Это было прекрасно, когда мы разрабатывали наш локальный экземпляр на наших машинах разработки. Но у нас есть несколько программистов, которым необходимо получить доступ к базе данных и логистику, чтобы все script их изменения и отбросить их в Subversion стал кошмаром. Независимо от нашего простого решения было поместить его на общий сервер в офисе и время от времени создавать резервную копию сервера, если кто-то испортил данные.

Ну, я испортил данные и их нужно было восстановить. К сожалению, у меня есть другой сотрудник в офисе, который работает над другим проектом и использует тот же сервер базы данных для разработки. Чтобы быть приятным, я бы хотел восстановить без перезапуска SQL Server и, возможно, нарушить его работу.

Есть ли способ script в T-SQL, чтобы иметь возможность получить эксклюзивный доступ или удалить все подключения?

Ответ 1

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

EXEC sp_dboption N'yourDatabase', N'offline', N'true'

Или вы можете

ALTER DATABASE [yourDatabase] SET OFFLINE WITH
ROLLBACK AFTER 60 SECONDS

Откат указывает, выполняется ли что-либо. После этого периода они будут отброшены назад. Таким образом, он обеспечивает некоторую защиту.

Жаль, что я не думал/читал правильно. Вы можете отключить онлайн и сделать резервную копию. Также была поставлена надпись "Переполнение" на T-SQL фрагмент для удаления всех подключений, а не сначала отключение в автономном режиме: Скрытые возможности SQL Server

Ответ 2

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

-- set single user, terminate connections
ALTER DATABASE [target] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE ...
ALTER DATABASE [target] SET MULTI_USER

"С немедленным откатом" является обязательным условием "прекращения". Оставляя это, ждет навсегда. Более приятная версия выше дает транзакции пользователя на несколько секунд для завершения.

ALTER DATABASE [target] SET SINGLE_USER WITH ROLLBACK AFTER 5

Оффлайн - хорошая идея, если вы хотите скопировать файлы базы данных, сценарий, который может быть полезен в настольных версиях SQL. Слишком тяжелый для этого сценария. Если в автономном режиме это было бы предпочтительнее. SQL отходит от sp_dboption.

ALTER DATABASE [target] SET OFFLINE WITH ROLLBACK AFTER 5

Ответ 3

@mattlant - это то, что я искал. Я привожу его сюда, чтобы он был в потоке.

Use Master
Go

Declare @dbname sysname

Set @dbname = 'name of database you want to drop connections from'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

Ответ 4

До сих пор это сработало для меня. Я щелкнул правой кнопкой мыши по базе данных > Задачи > Отсоединить...

Появился экран, позволяющий просматривать все активные соединения. Затем вы можете пройти и отключить каждое соединение. Когда вы нажмете ОК, вы отделили базу данных и вам нужно прикрепить базу данных. Щелкните правой кнопкой мыши Базы данных и выберите "Прикрепить", выберите файл mdf и добавьте db. На этом этапе у вас должен быть эксклюзивный доступ к восстановлению.

Примечание.. Я протестировал это, подключившись к одной из своих баз данных с моего локального компьютера, и с сервера сбросил подключения к моей базе данных, и я не потерял соединение с его базой данных.

Ответ 5

Я предлагаю поговорить с вашим коллегой и попросить его покинуть базу данных. (И заставьте его осознать проблему, потому что он может потерять изменения, которые он сделал при восстановлении.)

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

Ответ 6

Ну, вы можете убить SQL-процессы и сеансы с помощью KILL.

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

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

Ответ 7

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

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

Затем вы запустите процесс восстановления.