My SQL Server 2005 doesn't restore a backup because of active connections. How to force it?
Sorry, I do not see any kill connection button
Jader Dias
2009-07-20 15:25:22
It's not a button, it's on the dialog that pops up.
George Stocker
2009-07-20 16:13:46
Haha, I always use this method, always felt wrong to me, glad others do it too...
JonoW
2009-07-20 16:27:02
I think it's a little hacky myself; but it works.
George Stocker
2009-07-20 16:34:01
+5
A:
You want to set your db to single user mode, do the restore, then set it back to 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:BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:DataYourLDFFile.mdf'
/*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)
brendan
2009-07-20 15:25:20
Rather than issuing an IMMEDIATE ROLLBACK , it may be pertinent to only ROLLBACK after a specificed DELAY, thereby giving user queries an opporunity to complete naturally.
John Sansom
2009-07-20 16:02:24
Good point, updated to rollback to include the AFTER 60 command to allow current queries to complete
brendan
2009-07-20 16:08:47
+1
A:
Restarting SQL server will disconnect users. Easiest way I've found - good also if you want to take the server offline.
But for some very wierd reason the 'Take Offline' option doesn't do this reliably and can hang or confuse the management console. Restarting then taking offline works
Sometimes this is an option - if for instance you've stopped a webserver that is the source of the connections.
Simon_Weaver
2010-04-11 08:02:51