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

You can force the DB offline and drop connections with.

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 stackoverflow.com/questions/121243/hidde....

– RedWolves Oct 8 '08 at 16:52 Your link to the t-sql snippet is why I accepted this answer. – RedWolves Oct 8 '08 at 17:55 sp_dboption will be removed in a future version of MS SQL - version after 2008. Get used to alter database.

– Precipitous May 19 '09 at 6:43.

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.

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.

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.

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

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.

I'd suggest talking to your co-worker, and asking him to leave the database. (And make him aware of the problem, because he might lose 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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.