Database snapshot

Can database snapshot be used in restoring full backup?

more ▼

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

avatar 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/

more ▼

answered Feb 03, 2011 at 02:06 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

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 to get a better understanding

more ▼

answered Feb 03, 2011 at 02:06 AM

avatar image

26.2k 18 38 48

(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.

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: 1278 times

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

Copyright 2018 Redgate Software. Privacy Policy