How to Create/Restore a Database Snapshot?


In previous article we learned What is SQL Server Database Snapshot. Now in this article, we will show how to create and restore a Database Snapshot.

To create a database snapshot, we need to use the T-SQL. It is the only way to do it. You cannot create a Database Snapshot in the SSMS.

Creating a Database Snapshot

Here is a sample database snapshot create script on the AdventureWorks database.

Select the database and execute the below query to get logical name

SELECT name FROM sys.database_files WHERE type <> 1

Syntax:

CREATE DATABASE <database_snapshot_name> ON
(NAME = <database_logical_name>,
FILENAME = <'Path_of_snapshot_file_with_extention_ss'>)
AS SNAPSHOT OF <database_name>;

As you can see, the syntax is similar to a normal database creation except for two things:
1. We use the word AS SNAPSHOT OF DATABASE_NAME to specify the name of the database that requires a snapshot.
2. By default, it is better to specify the extension of the snapshot datafile as .ss (which means SnapShot.

Example:

CREATE DATABASE AWDB_Snapshot ON
(NAME = AdventureWorks_Data,
FILENAME = 'D:\Data\AWDB_Snapshot.ss')
AS SNAPSHOT OF AdventureWorks;

Restoring a Database Snapshot

If all the objects and data were dropped, it would possible to recover all the information from the snapshot to the original database.

Syntax:

RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>

To revert the AdventureWorks database to the database snapshot we created before, use the following T-SQL command:

Example:

USE master;
RESTORE DATABASE AdventureWorks
FROM
DATABASE_SNAPSHOT = 'AWDB_Snapshot';
GO

<<Click here to see all posts>>

If you found any of the information on this page helpful in anyway then please consider sharing this content with your favorite social network or by leaving your thoughts in the comment section. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.