Restoring Database from Snapshot on the different Server

  • Hi,

    I am a SQL DBA in our Company. I found lately that the Database Snapshots can be very useful for our testing environment.

    The only problem, I could not find a good way of restoring a snapshot backup from Live to the Testing Server.

    We are running SQL Server 2008 and 2012 databases mostly on the Enterprise editions.

    Any help (suggestions, scripts) will be greatly appreciated.

    Thank you.

    Alex

  • You can't, that's not how snapshots work.

    When you create a database snapshot, any changes you make to the source database of that snapshot, the original version of the page is then copied into the snapshot. Hence all that the snapshot contains is the original version of pages that have changed in the source DB since the snapshot was created.

    A revert to snapshot just copies those original pages back into the source database.

    Hence if you create a snapshot on your testing DB, it's tied to the testing DB, containing nothing more than the pages which have changed in that test DB. It can't be used to revert any other database because it only knows the changes made to the testing DB.

    Snapshots aren't backups.

    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
  • Thanks Gail,

    I probably did not make myself clear.

    My purpose is to take a backup of the Live database (that is over 100GB in size), while the site is under maintenance and the database is offline, and restore it over testing database on the other Server.

    So, I thought that I maybe can make a use of snapshots (or replicated snapshots), since they are quick in create and restore.

    There was an article Online but it was not clear as well.

    So, still no luck for me using Snapshots, right?

    Thank you.

    Alex

  • I understood you just fine. The only restore which a snapshot can be used for is to revert (restore) the source database to the time which the snapshot was created.

    A snapshot is quick to create, because minimal data is copied on creation. It's fast to revert to, because minimal data has to be copied back. Both of those are because a snapshot is not a backup, not a database, but a collection of the original version of pages which have been changed since it was created.

    If you want to take the live database and restore it to dev, you need to take a backup of the production database, copy the backup file to dev and then restore it. Normal backup database, restore database.

    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
  • Thanks a lot for your help Gail,

    It is crystal clear now.

  • I am exploring usage of snapshots as a mechanism for doing day to day row level delta dumps. I use the normal backup database, restore database to put copies of my databases onto another server where I do the delta dumps.

    Is it possible to restore not only the database but ALSO some subset of a database's snapshots from the prod to the second server? I know that it is not possible to do a full restore to a database that has snapshots referencing it. But is is possible to add a set of snapshots from the source server to the second server? From what I have been able to dig up so far, the snapshots do not appear to be "portable".

    • This reply was modified 1 month, 2 weeks ago by  joe-676144.
    • This reply was modified 1 month, 2 weeks ago by  joe-676144. Reason: more elaborate use case description
  • joe-676144 wrote:

    Is it possible to restore not only the database but also it's snapshots from the Live to the Dev server?

    It's about time you found this place and signed up, Joe.  Welcome aboard!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Have been signed up for a while... just never had to ask a question. I always seem to be able to find what I need from an existing thread.

  • It is not possible to port a snapshot - because the snapshot is entirely dependent on the source database.  The snapshot only contains the delta changes that have occurred in the source database since the snapshot was created.  When querying a snapshot...you are still querying the source database except where data has changed - which you then get from the snapshot database file.

    You also have to be aware that a snapshot will 'grow' over time - based on how much data is changed in the source database.  Eventually that can become an issue and impact the production system.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • Thanks Jeff.

    Your response confirms my suspicions about the feature. As I have limited familiarity, I wanted to make sure I wasn't missing something.

    Just to be clear.... if you will indulge me... let me pose my question another way by posing another use case.

    Lets say I want to migrate a database from server A to server B. Simple enough without snapshots, just backup the db on server A and restore from that backup onto server B. But let's say that the db on server A has a snapshot that I wish to preserve when migrated to server B. It appears there really is no way to do that. I am guessing that the nitty gritty behind why that is... is because the snapshot is a page based entity... and there is no page consistency maintained via the backup and restore... thus making it impossible for the snapshot to align with the new image on server B. Which would explain why there is no way to backup the snapshot.

Viewing 10 posts - 1 through 10 (of 10 total)

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