How to revert a Database Snapshot to recover a SQL Server database

By:   |   Comments (2)   |   Related: > Restore


A database snapshot is a very useful feature in SQL Server. This can be used to fulfill many requirements like point-in-time reporting requirements and reverting a database back to a point in time. In this tip I will explain how to revert a database snapshot to recover your database.


Database snapshots were introduced with SQL Server 2005 and this is still an enterprise only edition feature. This feature has solved many requirements like reporting, data safeguarding and point in time recovery if you do something wrong in your database. This allows us to decrease the database recovery time by reverting a database snapshot.

A database snapshot is basically a full database point-in-time view of a source database. As it's a point-in-time view of the source database, the snapshot data never changes if changes occur on the primary database. So when you revert a database snapshot, it will recover to the point in time when the database snapshot was created.

As changes are made to the source database, a copy of the original source data page is moved to the database snapshot sparse file, so the snapshot remains intact. This way a database snapshot provides the correct point in time data. One sparse file is associated with one data file, so if your source database has multiple data files, then you will have to create a separate sparse file for each corresponding data file.

Below is an example to test this scenario. Suppose you create a database snapshot before running a bulk DMLs on your database. If something goes wrong, then you can revert to this database snapshot to recover your database to the state it was before running the bulk DML.

There are some limitations when recovering using a database snapshots. You must drop all database snapshots except the one you want to recover when using a database snapshot to recover the source database.

Example Recovery Process

Here is a step by step process to create a snapshot, delete some data and then recover the database using a database snapshot.

Step 1 - Get Information About The Database

First we will check how many data files we have in our source database. Make sure to create a separate sparse file for each data file.


SP_helpdb to get the information about data files

Step 2 - Create New Database Snapshot

Since we have only have one data file, we will need to create the database snapshot with one sparse file. This command creates the database snapshot for the database.


creating dbsnapshot

 Here we can see we have 5 rows in this table.

Run select statement to see the no of rows before deleting the rows

Step 3 - Delete Data From a Table

Now I will delete all rows from a table, so that we can revert the database snapshot to recover the deleted rows.

Delete all rows from this table.

USE KPO_Master
DELETE tbladmin

Delete all rows from table tbladmin

Let's again run a SELECT statement to see the table contents.  We can see we have no data in this table.

SELECT all content of deleted table

Step 4 - Check To See If Data Still Exists In Snapshot

As you can see, all rows from tbladmin table have been deleted from the source database in the query above. Now you can run the same SELECT statement on the database snapshot. Here you can see that all of the data which we have deleted in the source database still exists. This is what we will recover using the database snapshot.

 Run SELECT on database snapshot

Step 5 - Drop All Other Snapshots For Database

If you have multiple database snapshots created then you must first drop all unwanted database snapshots before reverting it to the source database. To test this step, create another database snapshot using Step 2. I have created a separate database snapshot at 12 PM named "KPO_Master_DATABASESNAPSHOT_2012060212PM".

Now suppose you have multiple database snapshots from multiple source databases on your SQL Server Instance. First you have to find the correct database snapshots for your source database. As you can see the database ID is 45 for our source database which we can get in Step 1. Run the below command to get a list of all database snapshots for your source database.

SELECT name,database_id,source_database_id FROM sys.databases
WHERE source_database_id=45

Find all db snapshot information from sys.databases system table

Here you can see that two database snapshots are created for our source database. We must drop the unwanted database snapshot. As we have to recover our database from the first database snapshot that was created before dropping all rows from the above table. To drop a database snapshot run a normal drop database statement using the database snapshot name as shown below.


Step 6 - Restore Snapshot

The final step is to restore a database snapshot to our source database to recover all data.  It is a simple restore statement using the with DATABASE_SNAPSHOT option.


Reverting a db snapshot

Now you can run the SELECT statement again to check that the data was recovered.

SELECT statement after Reverting a db snapshot
Next Steps
  • Remember, all data which was updated after the database snapshot creation will be lost if you restore a database using a snapshot.
  • For this example, we only had a few rows and we could have selected the data from the Snapshot back to the Source database, but if this occurred for multiple tables or a large amount of data restoring a snapshot is a much simpler and cleaner approach.
  • Always make it a best practice to create a database snapshot before running any bulk operations to secure your database and reduce your recovery time.
  • Use database snapshots to run reports to reduce your source database load.
  • Read more about Database Snapshots

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Thursday, June 6, 2013 - 11:12:00 AM - Michael Brown Back To Top (25323)


I really liked this article and tried and tested the database snapshot. What a neat tool. We currently run a system of live update time and I was always nervous about what we would do if our database crashed. Now, I have a tool in place to recover by scheduling a task of a database snapshot hourly!!

Thank you very much!


Michael Brown

Monday, July 9, 2012 - 8:27:24 AM - Devesh Kumar Back To Top (18400)

Hello Sir,

I need info on the below point. Please reply as soon as possible.

> Setting up recovery mechanism if anything goes wrong.

My Email id is: [email protected]

Thanks in advance!


get free sql tips
agree to terms