DB Restore - Stopping All Connections

Question

DB Restore - Stopping All Connections

joshdbguy

SSCertifiable

Points: 7707
More actions
March 8, 2013 at 10:46 am

#290605

I'm working on database restores on a server and have run into some issues. We have multiple databases that are being slammed by applications at all points throughout the day. When doing a normal restore with "kill connections" through Redgate and/or SQL, the restores will fail about 50% of the time because of active connections. My new restore script is complicated but starts with setting each database to single_user. This has helped and now restores fail about 30% of the time but it's still not good enough.
I have what I believe to be one of two options, either find all of the tasks/apps that hit our databases, which will probably be a couple day process, and stop those during the restores. Or find a good way to shut down all connections to the entire server except for the restore process. I'd prefer to do the later but don't know of a good way to do it automatically and through SQL Server. Can anyone help?
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

doing the alter database [dbname] set single_user with rollback immediate is great, but only if your connection is using the database.

if you are looping thru them all, i can see where some other process could grab the single use connection.

I've seen examples where they set to single user, then rename the database as teh next statement...(ie DBName_Restoring that way other process don't find the database any more.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 2

Since you are going to restore the database anyways - this will work:

ALTER DATABASE {database} SET OFFLINE WITH ROLLBACK IMMEDIATE;

Take the database offline, perform your restore - and the database will come back ONLINE when you issue the RESTORE DATABASE {database} WITH RECOVERY; (if you do the RESTORE with NORECOVERY that is...if you don't include it, RECOVERY is the default).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

I usually do it this way, because once the database is dropped there is no way to connect to it.

use master;

alter database [MyDb] set offline with rollback immediate;

alter database [MyDb] set online with rollback immediate;

drop database [MyDb];

I set it back online so that when the database is dropped the database files are deleted. If you drop it while the database is offline the files are not deleted.

It's theoretically possible that someone could connect when you bring it back online and before the database is dropped, but I have never seen that happen when the commands are executed in one batch like the script above.