Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

DB Restore - Stopping All Connections Expand / Collapse
Posted Friday, March 8, 2013 10:46 AM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 1, 2016 4:17 PM
Points: 313, Visits: 725
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?

Post #1428700
Posted Friday, March 8, 2013 10:58 AM



Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 14,550, Visits: 38,420
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.


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!
Post #1428703
Posted Friday, March 8, 2013 11:39 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 4,377, Visits: 9,709
Since you are going to restore the database anyways - this will work:


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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1428714
Posted Friday, March 8, 2013 10:24 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, October 24, 2015 2:31 AM
Points: 3,158, Visits: 11,771
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.

Post #1428852
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse