question

hatake avatar image
hatake asked

Database snapshot

Can database snapshot be used in restoring full backup?
database-snapshot
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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: https://www.sqlservercentral.com/articles/database-snapshots-1

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
Excellent answer! I use snapshots in test environment to revert back to a point in time when a new test iteration starts / restarts
1 Like 1 ·
WilliamD avatar image
WilliamD answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.