So, I would like to have a snapshot structure like the following: A snapshot 1 is taken. Some code is run, then a snapshot 2 is taken.

Then some more code is run, and the database is reverted to snapshot 2. Then more code, then snapshot 2, N times.

Then, once all that is done, the database is restored to its original state, snapshot 1. Basically, I would like it to be the case that I do not need to delete existing snapshots when restoring a snapshot, but rather that snapshot 1 is "stored" in snapshot 2 somehow, and when snapshot 2 is loaded, snapshot 1 is available again for a subsequent restore.

It seems as though this should be possible, because if snapshot 1 is "frozen" immediately prior to snapshot 2 being created, then once snapshot 2 is reverted to, there should be nothing preventing snapshot 1 from working, since the database state is identical to when it was frozen and snapshot 2 was created.

Is this possible? Do I need to create a snapshot of a system table?

  • Why not just create multiple snapshots as needed? – clifton_h Sep 20 '18 at 22:37
  • Because in order to revert to snapshot 2, I have to delete snapshot 1 first. I would like to not delete snapshot 1, so that I can use it to restore my database. – Daniel Paczuski Bak Sep 20 '18 at 22:43
  • Maybe using a smaller subset of your database might allow you to just rollback transactions? The question is interesting, but the use case seems too shallow at present. Is ETL involved? What modifications are you doing so extensively that requires multiple snapshots? – clifton_h Sep 20 '18 at 22:59
  • How can one run a test on a snapshot? I think I'm confused! I would like to do this because I would like to have suite-level and case-level snapshots. So the suite snapshot is made, then the suite setup occurs, then the case snapshot is made. After every test, the case snapshot is reverted to, and at the end of the suite, the suite snapshot is reverted to. – Daniel Paczuski Bak Sep 20 '18 at 23:43
  • I don't think I can test directly on the snapshots because these are primarily tests on a web server which is connected to the primary database, and I can't reconnect the server to connect to the snapshot (if that's even possible) – Daniel Paczuski Bak Sep 20 '18 at 23:44

This can't be done, the snapshots are not 'tiered' such that snapshot 2 can revert back to the snapshot 1 state.

In SQL Server, database snapshots preserve the original state of each page only when it is changed in the source database. Snapshot 2 is not preserving changed pages from snapshot 1 but from the original source database.

The documentation highlights this (https://docs.microsoft.com/en-us/sql/relational-databases/databases/revert-a-database-to-a-database-snapshot?view=sql-server-2017) in step 2 under "How to Revert a Database to a Database Snapshot".

Your best bet would be to copy the original database, then take a snapshot of the copied database. Run your "snapshot 2" tests against the snapshot taken of the copied database. You can then easily revert the snapshot for multiple tests and return to your pre-test state and when you're finished, simply drop the copied database. In this fashion, your original database will be unaffected by your testing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.