How to Restore Database from Snapshot in SQL Server?

• Understanding Database Snapshots
• Reasons for Restoring Database from Snapshot in SQL Server
• Demonstrating an Example to Restore Database from Snapshot in SQL Server

• Create a Database Snapshot
• Make Changes to the Original Database
• Restore Database from Snapshot

• Alternative to Restoring Database from a Snapshot
• FAQs

Understanding Database Snapshots in SQL Server

A database snapshot is a useful feature that helps perform point-in-time recovery of your source database. A snapshot captures the current state of the database. So, if you do something wrong in a database, you can revert the database to the state it was in – when the snapshot was created. For instance, if you created a snapshot while performing a DML operation on a database, using the database snapshot helps restore the database to the state before the DML operation was performed.

Reasons for Restoring SQL Database from Snapshot in SQL Server

Several reasons might require you to restore database from snapshot in SQL Server. The most common reason is that reverting the database to a specific point in time from a snapshot is faster than restoring from a backup. In addition, in the event of a user error, such as accidentally dropping a table, you can recover the dropped table by reverting the database from a snapshot.

Restore Database from Snapshot in SQL Server

Let's discuss an example demonstrating the step-by-step process of creating a database snapshot, modifying the database, and reverting the database from snapshot.

Step 1 – Create a Database Snapshot

Here is the Transact-SQL (T-SQL) query to create a new database snapshot.

In this query, 'Test_Database' is the name of the database for which we are creating a snapshot. Further, 'Test_Database_dbss' is the name of the snapshot. Before executing the above query, make sure you replace the database name and database snapshot with the name of your database.

So now, let's run the T-SQL query, and you can see in the following screen that the commands are executed successfully.

Refresh the Database Snapshots folder.

On expanding the Database Snapshots folder, you can see that the snapshot of the 'Test_Database' is created. Expand it, and you will see one table which is the same as the original database. This is just for demo purposes.

Step 2 – Make Changes to the Original Database

Now let's make some changes to the original database. For instance, delete a table from the database. To do so:

• In SSMS, right-click on a database table and select DELETE.

• When prompted to delete the SQL database object, click OK.

• Right-click on Tables and click Refresh.

The table is deleted from the original database. Check if the table still exists in the database snapshot.

Note: If you've several database snapshots in SQL Server, ensure to drop the unwanted snapshots. That's because you can revert only one database snapshot at a time.

Step 3 – Restore Database from Snapshot

Here is the syntax for restoring a database from the database snapshot:

RESTORE DATABASE yourdb_name FROM DATABASE_SNAPSHOT =yourdb_snapshot_name

In our case, we'll be restoring 'Test_Database' from snapshot to recover the deleted records.

The commands are executed successfully, as you can see in the following screenshot.

Refresh your original database, and you will see that the deleted table 'CompanyDetails' is now restored.

A snapshot is dependent on the source database. So, if the database gets corrupted, you cannot restore the database from a snapshot.

Alternative to Restoring Database from a Snapshot

If you fail to revert a database to its current state from a snapshot, try restoring the database from the most recent backup. However, situations may arise when the backup you want to restore the database from has turned bad. You need to repair the database using DBCC CHECKDB with the minimum repair option as a last resort. However, using the repair options does not guarantee to preserve data integrity. Also, using the 'REPAIR_ALLOW_DATA_LOSS' option involves data loss risk.

A better alternative is to use Stellar Toolkit for MS SQL, a set of comprehensive tools designed for DBAs to repair a corrupted SQL database, extract data from a corrupted database, and reset a lost or forgotten password of SQL data files.

Read this: Stellar Toolkit for MS SQL – All in one Admin Utility