Undo with Database Snapshots

How to undo the data changes using Database Snapshots

In this blog post, I would like to talk about how can we revert or undo the data changes which might have happened by a user mistake. For example, if some one has deleted a useful data from the database (for which there is already a snapshot exists prior to the data deletion), we can get the data back using the database snapshots. In this post, I am not going to talk about how the database snapshots works and it's internals.

To demo this, let me create a database and create a table and populate with some data. To do this, you can use the script below.

CREATE DATABASE VSNPSHT
GO
USE VSNPSHT
GO
CREATE TABLE VSample (id int, name char(10))
GO

So, we have the table created now. Let me populate it with some data. To do this, just execute the below T-SQL Statement.

INSERT INTO VSample VALUES (1, 'V')
GO 100

Let me now create a Database Snapshot for this database.

CREATE DATABASE VSNPSHT_SNAPSHOT ON
( NAME = VSNPSHT, FILENAME = 'C:\MSSQL\DATA\VSNPSHT_DB_SNAPSHOT.ss')
AS SNAPSHOT OF VSNPSHT
GO

Here, the NAME refers to the logical file name of the data file, and the filename is used to indicate the location for the snapshot file to be created. We can view the snapshot of this database under the path Databases -> Database Snapshots in SQL Server Management studio as shown below.

To access the data from the snapshot, you need to use the database name in the query before the table or the schema name as shown below.

select * from [VSNPSHT_SNAPSHOT].[dbo].[VSample]

Alternatively, you can select the database context to the Snapshot in the QueryAnalyser to select the data from the snapshot.

Now, let me delete the data from the table in the database.

Delete from [VSNPSHT].[dbo].[VSample]

Now, to get the data back to the database using the snapshot, execute the T-SQL Statement below. This statement looks similar to a restore statement.

USE master;
RESTORE DATABASE [VSNPSHT] FROM DATABASE_SNAPSHOT = 'VSNPSHT_SNAPSHOT';
GO

We all are set. Now we should get the data back and you can verify it through a select statement on the table. But, keep in mind that this option would bring the database back to the state when the snapshot was created. So, if you are looking for any specific table's data to be recovered, this option may not work as described here. You need to do some more operations for that like export the data from the snapshot table to a temp table and then import the desired data to the live table.

Hope this helps you.. Thanks for reading.