How to revert a database to a snapshot version of database

Problem: How to revert a database to a snapshot of previous versions of database?

Solution: Revert a Database to a Database Snapshot

Database snapshots present on a database can be used to revert the database to it previous state (at the time of snapshot is created)

To Revert the database to its snapshot, there are few pre-requisites:

1.     Only one version of the snapshot should exist in the database snapshot folder.

2.     Database should not be offline.

3.     Database or snapshot files should not be corrupted.

4.     Snapshot database should not be in using state.

5.     Source Database should not have any active connections.

Note: you will lose the changes in between the snapshot and your latest database and so in a safer side take the full database backup and log backup, to get to the most recent transactions.

Syntax:

RESTORE DATABASE database_name FROM DATABASE_SNAPSHOT =database_snapshot_name

If you want to more about how to create a database snapshot, please go through this article. how to create a database snapshot

Example:

In this example, I would like to revert a database to an existing latest snapshot.

RESTORE DATABASE TEST from  

DATABASE_SNAPSHOT = 'Test_SSEvening'; 

GO

You might get the below error if you have more than one version of the database snapshots.

--Msg 3137, Level 16, State 4, Line 28

--Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.

--Msg 3013, Level 16, State 1, Line 28

--RESTORE DATABASE is terminating abnormally.

So the next step is to clean up the snapshots to have only the last recent snapshot.

DROP DATABASE Test_SSEvening_old;

GO

use master

RESTORE DATABASE TEST from  

DATABASE_SNAPSHOT = 'Test_SSEvening'; 

GO

OUTPUT:

Table A Before reverting the database to snapshot:

Table A After reverting the database to the snapshot: