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.
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.
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.
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.
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.
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.
Technology You Can Trust A Brand Present Across The Globe
This website uses cookies in order to provide you with the best possible experience and to monitor and improve the performance of the site in accordance with our cookie policy.
This website uses cookies in order to provide you with the best possible experience and to monitor and improve the performance of the site in accordance with our cookie policy.
The software is not for Mobile. You can download the software on Windows Desktop or Laptop. Enter your Email ID below to get the download link.
Email Sent Successfully
Check your email. If you didn't find the email, check the spam/junk folder. Still not there? Please whitelist stellarinfo.com and request to send the link again.
After getting the email, follow these instructions:
-535.jpg&url=https://www.stellarinfo.com/article/restore-database-from-snapshot.php)
9 min read
-1112.jpg)
