File Repair

How to Restore Database from Snapshot in SQL Server?

Summary: This article outlines the reasons for restoring a database from a snapshot in SQL Server. Also, it explains the step-by-step process of creating a database snapshot and how to restore it. Further, it discusses using Stellar Toolkit for MS SQL software to restore the database when reverting from a snapshot won’t work. Download the demo version of the software from below to test its features.

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.

CREATE DATABASE Test_Database_dbss ON
( NAME = Test_Database, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.STELLAR\MSSQL\DATA\Test_DatabaseCurrent.ss')
AS SNAPSHOT OF Test_Database;
GO

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.

query to create database snapshot

Refresh the Database Snapshots folder.

refresh database snapshot

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.

database snapshot created

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.
delete database table
  • When prompted to delete the SQL database object, click OK.
delete sql database object
  • Right-click on Tables and click Refresh.
refresh database tables

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

check data in 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.

USE master;
RESTORE DATABASE [Test_Database] from
DATABASE_SNAPSHOT = 'Test_Database_dbss';
GO

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

restore database from snapshot

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

database restored from snapshot

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

FAQs

Q. Can I restore a database snapshot to a different location?

A. No, you can restore a database snapshot to a different location because a snapshot resides on the same SQL Server instance as the source database.

Q. I occasionally run a DROP operation to drop unwanted database tables. However, I need to recover a dropped table as it contains data that I still need. Can I recover the table data by reverting the database from a snapshot?

A. Yes, you can recover the dropped table by reverting the database from a snapshot.

Q. How can I use the Stellar Toolkit for MS SQL software to restore a database and recover the data?

A. The SQL toolkit comprises a SQL repair tool and a Backup Extractor tool. If a database becomes inaccessible or corrupted, you can use the SQL repair tool to fix corruption and restore the database to its original state.

If you need to restore a database from a backup and it is corrupted, you can use the Backup Extractor tool in the toolkit to extract and recover the data.

progress
77% of people found this article helpful