If you need to restore a Microsoft SQL Database, you might run into the problem that there are still users connection to the database.
It’s not easy to drop those connections… lots of info that I find on google use
1 2 3 |
ALTERDATABASE DB_NAME SET SINGLE_USER WITHROLLBACK IMMEDIATE --do stuff ALTERDATABASEDB_NAME SET MULTI_USER |
And this doesn’t always seem to work on SQL2008 or SQL2012.
But… here’s a handy script that I found on Stackoverflow:
Replace the DB_Name variable with the name of your DB for which you want to drop the connection:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
USE master GO SET NOCOUNT ON DECLARE @DBName varchar(50) DECLARE @spidstr varchar(8000) DECLARE @ConnKilled smallint SET @ConnKilled=0 SET @spidstr = '' Set @DBName = 'DB_NAME' IF db_id(@DBName) < 4 BEGIN PRINT 'Connections to system databases cannot be killed' RETURN END SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; ' FROM master..sysprocesses WHERE dbid=db_id(@DBName) IF LEN(@spidstr) > 0 BEGIN EXEC(@spidstr) SELECT @ConnKilled = COUNT(1) FROM master..sysprocesses WHERE dbid=db_id(@DBName) END |