0

I noticed I cannot have multiple snapshots of a SQL Server database and then restore from one of them (without I believe, deleting all but the one I want to restore).

Is there a way to keep multiple snapshots of the same database and be able to restore to any of them?

Or is the only way either buying some 3rd party software which does this, or taking full backups of each version of the database?

  • You are correct, you can have multiple snapshots but if you want to restore one you must first remove all others. There are 3rd party products that do this. – Sean Gallardy Mar 29 '16 at 3:28
2

DB snapshots work on the basis of copy-on-write. Read operations of data that is unchanged since the snapshot was taken are fulfilled by pages from the original database. If the data has been changed, the original version of the data is copied to the snapshot so future queries will use pages in the snapshot. When you have multiple snapshots, there is a potentially intricate collection of pages in each snapshot depending on how much write activity took place and how much of that was on the same data. You could end up with many generations of data where each generation resides in one of the snapshots.

This means all snapshots are individually, tightly-coupled with the original database. If you restore the original database using one of the snapshots, that effectively breaks the coupling for all other snapshots hence it isn't allowed.

Third party solutions from storage vendors keep copies of the data around by retaining and tracking blocks that each snapshot is dependent on. That means you could end up consuming storage space many times the size of the original database depending on how many snapshots you have and restores you make. It gets expensive and very complicated quickly.

If you're using multiple snapshots to get different point-in-time data, suggest you consider temporal tables in SQL Server 2016.

  • Thanks! I look forward to using SQL Server 2016 as well. – Maximojo Mar 30 '16 at 2:51

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.