Post reply

Revert a database to a DB Snapshot

  • August 19, 2019 at 11:02 am

    #3672243

    We are a product based company and using SQL Server as backend. Whenever there is a new version of OUR product released, we will upgrade the product for all our SaaS customers  and upgrading the product across all the SaaS Customers have been automated.

    As a pre-upgrade process, we will take DB Snapshot and if any issues with the Product Upgrade, we will revert the DB using the DB Snapshot.

    Many times , we will be handling high volume databases (>500gigs). While reverting the snapshot on these kind of bigger databases, we are not able to find the restoring % completed. Is there any way we can identify the percentage of restoring completion while doing it from the snapshot (we are NOT restoring the FULL backup where we can identify the %completed using percent_complete column in sys.dm_exec_requests table)

     

     

     

  • August 20, 2019 at 11:10 am

    #3672692

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 20, 2019 at 2:14 pm

    #3672782

    Have you tried doing the snapshot restore WITH STATS?

     

    Never had anyone worry about this, and I can't seem to find out if this is tracked. I'd have thought dm_exec_requests had this. Is there no entry during revert?

  • August 28, 2019 at 2:28 pm

    #3675110

    Thanks for your reply Jones.

    I got this error message while trying WITH STATS = 5 --"The revert command is incorrectly specified. The RESTORE statement must be of the form: RESTORE DATABASE <x> FROM DATABASE_SNAPSHOT = <y>."

    'percent_complete' column in dm_exec_requests DMV shows 0% till the end of the restoration.

    Currently the workaround to check whether the snapshot is getting restored or not is, check the disk activity for the snapshot in ResourceMonitor.

    Any help would be appreciated.

    Thanks.

     

     

     

  • August 29, 2019 at 1:30 am

    #3675253

    Asking a question of MS.

  • August 30, 2019 at 1:54 am

    #3675595

    Here's what I got about the query of capturing percentage of time:

    "I don't believe so IIRC, as the revert is all done down in the file manager level (read from snapshot, write to real DB, in a tight loop essentially) and not by the restore code. Technically you could look at the size of the snapshot, divide by 8k, and then have an XEvents session counting the async writes to the real DB, but that's a little bit overkill."

    Maybe this is something you can use to track the progress.

Viewing 6 posts - 1 through 5 (of 5 total)

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