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?
Best Answer
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 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.