Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (2)   |   Related Tips: More > Restore


Latest on-demand video "Optimize SQL Server Performance" (watch now for free)


Problem

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.

Solution

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.

EXEC SP_HELPDB KPO_MASTER

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.

CREATE DATABASE KPO_Master_DATABASESNAPSHOT_201206029PM
ON
(NAME =KPO_MASTER_DATA,
FILENAME = 'E:\DATABASESNAPSHOT\KPO_Master_DATABASESNAPSHOT_201206029PM.dbsnapshot')
AS SNAPSHOT OF KPO_Master

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

DROP DATABASE KPO_Master_DATABASESNAPSHOT_2012060212PM

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.

RESTORE DATABASE KPO_Master 
FROM DATABASE_SNAPSHOT='KPO_Master_DATABASESNAPSHOT_201206029PM'

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


Last Update:


signup button

next tip button



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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 06, 2013 - 11:12:00 AM - Michael Brown Back To Top

 

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 09, 2012 - 8:27:24 AM - Devesh Kumar Back To Top

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: deveshk05@gmail.com

Thanks in advance!

 


Learn more about SQL Server tools