Post reply

Drop Database before Restore - Any Downside ?

  • July 8, 2013 at 7:12 am

    #277100

    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

  • July 8, 2013 at 7:19 am

    #1630428

    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, MVP, M.Sc (Comp Sci)
    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

  • July 8, 2013 at 7:41 am

    #1630442

    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

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

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