Jak podczas przywracania kopii zapasowej odłączyć wszystkie aktywne połączenia?

Chcesz ustawić db na tryb pojedynczego użytkownika, wykonaj restore, a następnie ustaw go z powrotem na multiuser:

ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK AFTER 60 --this will give your current connections 60 seconds to complete

--Do Actual Restore
RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUp\YourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\Data\YourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\Data\YourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.  If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER
GO

Reference : Pinal Dave ( http://blog.SQLAuthority.com )

Oficjalne odniesienie: https://msdn.microsoft.com/en-us/library/ms345598.aspx

Ten kod zadziałał dla mnie, zabija wszystkie istniejące połączenia z bazą danych. Wszystko co musisz zrobić, to zmienić linię @ dbname = 'databaseName' tak, aby miała nazwę twojej bazy danych.

Use Master
Go

Declare @dbname sysname

Set @dbname = 'databaseName'

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

Po tym udało mi się go przywrócić

Ponowne uruchomienie serwera SQL spowoduje odłączenie użytkowników. Najprostszy sposób, jaki Znalazłem-dobry również, jeśli chcesz wziąć serwer w trybie offline.

Ale z jakiegoś bardzo dziwnego powodu opcja 'Take Offline' nie robi tego niezawodnie i może zawiesić lub pomylić konsolę zarządzania. Ponowne uruchamianie, a następnie pobieranie prac offline

Czasami jest to opcja - jeśli na przykład zatrzymałeś serwer WWW, który jest źródłem połączeń.

Spróbuj tego ...

DECLARE UserCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT
    spid
FROM
    master.dbo.sysprocesses
WHERE DB_NAME(dbid) = 'dbname'--replace the dbname with your database
DECLARE @spid SMALLINT
DECLARE @SQLCommand VARCHAR(300)
OPEN UserCursor
FETCH NEXT FROM UserCursor INTO
    @spid
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQLCommand = 'KILL ' + CAST(@spid AS VARCHAR)
    EXECUTE(@SQLCommand)
    FETCH NEXT FROM UserCursor INTO
        @spid
END
CLOSE UserCursor
DEALLOCATE UserCursor
GO

Żaden z nich nie działał dla mnie, nie mógł usunąć lub odłączyć obecnych użytkowników. Nie widać też żadnych aktywnych połączeń z DB. Ponowne uruchomienie SQL Server (kliknij prawym przyciskiem myszy i wybierz Restart) pozwoliło mi to zrobić.

Aby dodać do porad już udzielonych, jeśli masz aplikację internetową działającą przez IIS, która korzysta z DB, może być również konieczne zatrzymanie (nie recykling) puli aplikacji podczas przywracania, a następnie ponowne uruchomienie. Zatrzymanie puli aplikacji zabija aktywne połączenia http i nie pozwala na więcej, co w przeciwnym razie może doprowadzić do wyzwalania procesów, które łączą się i tym samym blokują bazę danych. Jest to znany problem na przykład z systemem zarządzania treścią Umbraco podczas przywracania its database

Natknąłem się na ten problem podczas automatyzacji procesu przywracania w SQL Server 2008. Moje (udane) podejście było mieszanką dwóch udzielonych odpowiedzi.

DECLARE @SPID int = (SELECT TOP 1 SPID FROM sys.sysprocess WHERE dbid = db_id('dbName'))
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = top 1 spid from master.dbo.sysprocesses
        where dbid = db_id('dbName')
End

Następnie ustawiam bazę danych na tryb single_user

ALTER DATABASE dbName SET SINGLE_USER

RESTORE DATABASE and whatnot

Kill the connections again

(same query as above)

I ustaw bazę danych z powrotem na multi_user.

ALTER DATABASE dbName SET MULTI_USER

W ten sposób zapewniam, że nie ma żadnych połączeń zatrzymujących bazę danych przed ustawieniem na tryb pojedynczy, ponieważ pierwszy zostanie zamrożony, jeśli istnieją.

Żadne z powyższych nie zadziałało na mnie. Moja baza danych nie wykazała żadnych aktywnych połączeń przy użyciu Monitora aktywności lub sp_who. Ostatecznie musiałem:

• Kliknij prawym przyciskiem myszy węzeł bazy danych
• Wybierz " Odłącz..."
• Zaznacz pole "Drop Connections"
• Reattach

Nie jest to najbardziej eleganckie rozwiązanie, ale działa i nie wymaga ponownego uruchamiania serwera SQL (nie jest to dla mnie opcja, ponieważ serwer DB hostował kilka innych baz danych)