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

Drop Database before Restore - Any Downside ? Expand / Collapse
Author
Message
Posted Monday, July 8, 2013 7:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:48 PM
Points: 2,834, Visits: 8,542
I have a job that copies the production database backup to a reporting server every night, and then restores over the reporting database. Before the restore runs, there is a process to kill any connections. Rarely, but annoyingly, the restore fails because it could not get an exclusive lock on the database. I assume something is grabbing a lock right after the "kill spid" step runs and before the restore starts.

Is there a better way to make sure my restore does not fail ?

If I drop the database first, I may still have to kill connections. BOL says:
".. You cannot drop a database currently being used. This means open for reading or writing by any user. To remove users from the database, use ALTER DATABASE to set the database to SINGLE_USER. .."

Thanks



Post #1471171
Posted Monday, July 8, 2013 7:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 40,390, Visits: 36,823
If you drop the DB then the first thing SQL has to do is recreate the files, including zeroing them out.

Try taking the DB offline with rollback immediate.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1471176
Posted Monday, July 8, 2013 7:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:48 PM
Points: 2,834, Visits: 8,542
I will put this directly before the restore:

ALTER DATABASE MyDatabase SET OFFLINE WITH ROLLBACK IMMEDIATE

That should be more reliable than the "kill @spid" loop currently in place.

Thanks



Post #1471192
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse