Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 11:54 AM
Points: 142, Visits: 290
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.
Post #1428700
Posted Friday, March 8, 2013 10:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 12,903, Visits: 32,144
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
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
Problems are opportunites 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: 2 days ago @ 11:09 PM
Points: 3,108, Visits: 11,502
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