Geeks With Blogs
AngelEyes on .Net Tips, tricks and WTFs about Asp .Net, SQL Server and the likes.

The following code is from Ralph Whitbeck's blog. This is really a "cut and save" for me :)

When restoring an SQL Server Database, you'll often need to kill existing connections to it, before starting the task.

Change databaseName To the actual database name.

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

 

Posted on Wednesday, February 24, 2010 11:50 AM | Back to top


Comments on this post: Kill connections to resote DB - SQL Server

# re: Kill connections to resote DB - SQL Server
Requesting Gravatar...
Thanks, nice tip!
Left by Morteza on Aug 22, 2010 11:45 AM

# re: Kill connections to resote DB - SQL Server
Requesting Gravatar...
thanks indeed for you help
Left by Khaled EL Sheikh on Oct 28, 2013 8:15 AM

Your comment:
 (will show your gravatar)


Copyright © AngelEyes | Powered by: GeeksWithBlogs.net | Join free