SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB Restore - Stopping All Connections


DB Restore - Stopping All Connections

Author
Message
joshdbguy
joshdbguy
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 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?

Thanks.
Lowell
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29250 Visits: 39985
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 3188
Jeffrey Williams 3188
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8061 Visits: 9974
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 opportunities brilliantly disguised as insurmountable obstacles.

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

Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6004 Visits: 11771
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search