How to Gain Exclusive Access to SQL Server 2005 DB to restore?

Question

Whenever I restore a backup of my database in SQL Server I am presented with the following error:

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.

Usually to get around this I just restart the server. This was fine when we were developing on our local instance on our dev machines. But we have a few programmers that need to access the database and the logistics of having everyone script their changes and drop them into Subversion was becoming a nightmare. Regardless our simple solution was to put it on a shared server in the office and backup the server occasionally incase someone screwed up the data.

Well I screwed up the data and need to restore. Unfortunately, I have another co-worker in the office who is working on another project and is using the same database server for development. To be nice I'd like to restore without restarting the SQL Server and possibly disrupting his work.

Is there a way to script in T-SQL to be able to take exclusive access or to drop all connections?

Answer 1

I'd suggest talking to your co-worker, and asking him to leave the database.
(and make him aware of the problem, he might be loosing changes he's made when you restore.)

That's far better then dropping his connections, or setting exclusive access which might cause him some inconvenience.

Answer 2

So far this worked for me. I right clicked on the database > Tasks > Detach...

This brought up a screen that allows you to view all active connections. You can then go through and disconnect each connection. When you hit ok you've detached the database and need to Attach the database. Right-click on Databases and choose attach, pick you mdf file and the db is attached. At this point you should have exclusive access to restore.

Note: I tested this by connecting to one of his databases from my local machine and from the server dropped the connections to my database and I didn't lose my connection to his database.

Answer 3

Well you can kill sql processes and sessions with KILL:

http://msdn.microsoft.com/en-us/library/ms173730.aspx

But if you just drop all his current connections, won't he just reopen them?

You probably just have to go tell him you're going to restore from a backup so he stops connecting for a bit.

Answer 4

You can force the DB offline and drop connections with :

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

Or you can

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

Rollback specifies if anything is executing after that period they will be rolled back. So it provides some protection.

Sorry i wasnt thinking/reading right. Yoiu could bing back online and backup. There was also a post on SO on a TSQL snippet for dropping all connections rather than binging offline first http://stackoverflow.com/questions/121243/hidden-features-of-sql-server#121927

Answer 5

First, you cannot restore a database unless you are the only person currently connected and you have Admin rights. You must first tell your co-worker that you need to restore and ask him or her to be sure to script out any changes that might not be on the backup media. This is only polite and keeps co-workers from killing you.

Next you set the database to single user mode. You can look up how to do this in Books Online. This prevents anyone else from connecting while you are doing this and gives you a chance to kill existing connections. It is important to go to single user mode because no one else should be doing anything to the database while you restore.

Then you run the restore process.

Answer 6

@mattlant - that's what I was looking for. I bring it over here so it's in the thread.

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

Answer 7

I find this vastly faster and generally better than taking offline. Do read about it in MSDN so you understand the caveats. If using aysnc statistics, you have to turn those off, as well.

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

The "with rollback immediate" is the essential "termination" clause. Leaving it out waits forever. A nicer version of the above gives user transactions a few seconds to terminate.

ALTER DATABASE [target] SET SINGLE_USER WITH ROLLBACK AFTER 5

Offline is a good idea if you want to copy database files around, a scenario that can be handy in desktop editions of SQL. Too heavy for this scenario. If offline, this would be preferred. SQL is moving away from sp_dboption.

ALTER DATABASE [target] SET OFFLINE WITH ROLLBACK AFTER 5