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
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
Here we can see we have 5 rows in this table.
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
Let’s again run a SELECT statement to see the table contents. We can see we have no data in this 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.
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
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’
Now you can run the SELECT statement again to check that the data was recovered.
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
