Allow backup/restore Snapshot databases - by way0utwest

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 533288 Comments
Status Closed Workarounds
Type Suggestion Repros 0
Opened 2/12/2010 10:03:20 AM
Access Restriction Public


Currently we cannot back up a snapshot database. So the usefulness of the snapshot in terms of point in time query is extremely limited. Any issue with the db, such as a restore, DR, etc., results in a loss of the snapshot.
Sign in to post a comment.
Posted by Kevin [MSFT] on 10/23/2012 at 9:47 AM
Thank you for your suggestion, however this is not on our roadmap for any near-term releases.
I am accordingly closing this DCR.
Posted by Quantum John on 2/8/2011 at 12:54 PM
Option 3: Have a process with the effect of a backup-restore, where the GUI allows you to set a database snapshot as the source and new database name as the target (with optional specification of a different instance of SQL Server for the target). The process will then create an empty database, script out all the tables, views, sp's, users, permissions, etc., apply those scripts on the new database, then run a set of SELECT INTO commands to copy the data.

We can do this now manually, but it's a royal pain to code it all and maintain the code. Automation with via a GUI would be a HUGE improvement.
Posted by way0utwest on 1/7/2011 at 7:58 AM
Kevin, for simplicity, I would like to see #2 that you have there. This could be useful in any number of places, not the least of which is DR/forensic analysis where you might take a snapshot before changing something, and then realize you want to persist that state.

Just reading and writing out the pages from a snapshot (either the sparse file or the parent db files) should not be more much more complicated than a backup today.
Posted by AlanBarber on 2/23/2010 at 5:15 PM
I'm adding a vote for option #1.

I can see it being very handy to have a DB backup contain all aspects of a db including any snapshots tied to it at that point of time backup.

it would be very benificial to make sure that the restore of the snapshots if they exists is an option and not automatic, just in case there would be a need to not restore them.


Posted by Kevin [MSFT] on 2/22/2010 at 10:07 AM
We will look into implementing this in a future release, however it is not a small change, so it needs to be weighed against other cool work we could be doing.

Kevin Farlee
Posted by Kevin [MSFT] on 2/18/2010 at 11:38 AM
The case where #1 is interesting is where a company is using a DB Snapshot as a fixed point in time to do something like quarter-end reporting. If they lose their database, they don't have a way to get back to that point in time and finish the reporting unless they also took a full backup at the same point in time.
Posted by way0utwest on 2/17/2010 at 9:44 AM
Correct, I would not expect a backup of a snapshot to somehow still be linked to the source db (and use less space). Instead I would expect that it would be like #2, an independent database.

#1 would be interesting, though I wonder if this would cause more confusion/problems with users understanding. It's certainly preferable to me, but #2 might be a better implementation as a way to preserve snapshots. It would also allow me to go back in case of corruption in the primary, assuming I had a backup prior to corruption occurring.
Posted by Kevin [MSFT] on 2/17/2010 at 9:21 AM
Thanks for your suggestion.

DB snapshots are unique in that they really can't exist independent of the database that they are based on, because of the way that they are linked to it, the two must be in sync.

I see two options:
1: Allow the snapshot DB to be backed up as if it were part of the parent DB. So, if you restored a database which had had snapshots at the time of backup, you would get both the root DB and any snapshots as well when you restored. This would mean that snapshots could not be restored independently of the database they are based on.

2: Convert the backup of a DB snapshot to a full database backup, backing up all data blocks, either from the snapshot file or the parent's live DB files. This would mean that if you backed up a database and 3 snapshots, and then restored them all, they would come back as 4 independent databases, potentially taking up 4 times as much space as the original.

Kevin Farlee