- Understanding Database Snapshots
- Reasons for Restoring Database from Snapshot in SQL Server
- Demonstrating an Example to Restore Database from Snapshot in SQL Server
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;
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.
RESTORE DATABASE [Test_Database] from
DATABASE_SNAPSHOT = 'Test_Database_dbss';
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.
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.