Database snapshot

Can database snapshot be used in restoring full backup?
more ▼

asked Feb 03, 2011 at 01:51 AM in Default

hatake gravatar image

18 3 3 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If all you have is the snapshot file, you can't use it to restore a database, since its files will only contain those pages that have been changed in the original since you created the snapshot.

But if you want to revert a database to the point in time when you created the snapshot, you can use your snapshot in a RESTORE command:

RESTORE DATABASE database_name FROM DATABASE_SNAPSHOT = 'database_snapshot_name'

There's an SQL Server Central article about database snapshots, how they are built and how they are used to revert a db: [http://www.sqlservercentral.com/articles/Database+Snapshots/63004/][1]

[1]: http://www.sqlservercentral.com/articles/Database+Snapshots/63004/
more ▼

answered Feb 03, 2011 at 02:06 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

Excellent answer! I use snapshots in test environment to revert back to a point in time when a new test iteration starts / restarts
Feb 03, 2011 at 02:13 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left

You can only use a database snapshot on the instance you created it on.

Think of it as a sort of undo file. You take a snapshot of a database and then run a monster update script (using multiple transactions). The monster script is wrong and breaks your system. You can then "undo" the changes using the snapshot - similar to a restore, just a whole lot faster.

A database snapshot is based on sparse files (NTFS feature) which basically keeps track of changed data in an extra file. When you create a snapshot a sparse file is created and the changes in the database are tracked in there. This is why you can make a snapshot of a multiple TB database in an instant.

Because the sparse file is directly connected to the actual database files (MDF), you cannot use the snapshot directly against another database or backup. This is also the reason why only one snapshot can be created per database.

You could restore a database alongside a snapshot and run a data comparison, but that would use twice the disk space - once for the database + snapshot and once more for the parallel restore.

Read [How Database Snapshots Work][1] to get a better understanding

[1]: http://msdn.microsoft.com/en-us/library/ms187054.aspx
more ▼

answered Feb 03, 2011 at 02:06 AM

WilliamD gravatar image

25.9k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 03, 2011 at 01:51 AM

Seen: 804 times

Last Updated: Feb 03, 2011 at 01:51 AM