Database Snapshot 101


Database Snapshot is a feature available with the release of SQL Server 2005 and it is available in Enterprise and Datacenter editions. It provides a transitionally consistent copy of the source database, at the time of the Snapshot creation. Database Snapshot depends on the Source Database; in case the source database is not available, you cannot access the data from the Snapshot as well. SQL Server Books Online has lots of information about Database Snapshot; you can read more about it here. Database Snapshot is not a replacement of the existing Backup process, keep in mind that, it only works till your database is online.

Creating a Database Snapshot

There is no GUI option available in SSMS to create a database snapshot Sad smile. I know, if you are like me (GUI Lover), then it’s time to learn a little t-sql code. We are going to document some of the commands requires to configure database Snapshot. To create a database Snapshot, we are going to use the following command:

-- Create a Database Snapshot
CREATE DATABASE [SKGTEST_SS] ON
(NAME='SKGTEST', FILENAME='E:\SQL SERVER\USERDB\DATA\SKGTEST_SS.ss')
AS SNAPSHOT OF [SKGTEST];
GO

The above query creates a database snapshot of the database SKGTEST. If you have a closer look on the above query, you will find that, snapshot is depended on the data file present in the database. Another point to consider is, that logical data file name of the database and snapshot database should be same. In the below example, we are going to create a database snapshot of a database with multiple data files.

-- Create a Snapshot of the Database SKG_TEST2
USE [MASTER];
GO
CREATE DATABASE [SKG_TEST2_SS] ON
(NAME='SKG_TEST2', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST.SS'),
(NAME='SKG_TEST2_DATA2', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST_DATA2.SS'),
(NAME='SKG_TEST2_DATA3', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST_DATA3.SS')
AS SNAPSHOT OF [SKG_TEST2];
GO
Modify the Data in the Source Database

Once the snapshot is created, lets run some test on it. We will start with modifying a row. I am going to update the third row in the demo table. After updating it, let’s have a look on the row, both in Source Database and Snapshot respectively.FirstUpdate

As we have seen from the above screenshot, the data in the original row is moved to the snapshot database and the new data is updated in the Source Database. Let’s try to update the same row again, and see what happened:SecondUpdate

As you see, the data in the Snapshot didn’t change, only the source database is updated.

Inserting New Rows to the Source Database

If you are going to add new data to the source database, it will be available in the Source database but not in the snapshot. However, if you create a new snapshot of the database, the newly added data will be available in the latest snapshot. I have added two new rows and created a second snapshot of the source database. Later, I have removed two rows from the source database. Let’s check the deleted data from Source Database, First Snapshot database, and Second Snapshot database: AllDeletedRecords

Restoring from a Snapshot

Restoring is easy, execute the restore database command, and your database will be reverted back to the time, when the snapshot was taken. The syntax of the command is:

USE [master];
GO
RESTORE DATABASE [SKG_TEST2]
        FROM DATABASE_SNAPSHOT = [SKG_TEST2_SS];
GO

. If you try to execute the restore command, you will get the following error:

Msg 3137, Level 16, State 4, Line 1
Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

If you read the above message, all it says that there are multiple snapshot exists for the given database. To complete the restoration, you need to drop all the snapshots, expect the one from which you want to restore. Once I dropped the snapshot (which is not required), and re-execute the above restore command. The database was restored successfully from the database snapshot. Here is the script to create this scenario, Please execute it on your Lab environment. The script is for learning purpose only.

----------------------------------------
/* Database Snapshot Demo Script */

-- Author: Sudeepta Ganguly (@skganguly)
----------------------------------------
USE [master];
GO

-- Drop the Database Snapshot and the Source Database
IF (DATABASEPROPERTYEX('SKG_TEST2_SS', 'VERSION') > 1)
	DROP DATABASE [SKG_TES2T_SS];
GO
IF (DATABASEPROPERTYEX('SKG_TEST2', 'VERSION') > 1)
	DROP DATABASE [SKG_TEST2];
GO

-- Create the Database with Multiple Files
USE [MASTER];
GO
CREATE DATABASE [SKG_TEST2] ON PRIMARY
(NAME='SKG_TEST2', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST2.MDF',
 SIZE = 3MB, FILEGROWTH = 3MB, MAXSIZE = UNLIMITED),
(NAME='SKG_TEST2_DATA2', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST2_DATA2.NDF',
 SIZE = 3MB, FILEGROWTH = 3MB, MAXSIZE = UNLIMITED),
(NAME='SKG_TEST2_DATA3', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST2_DATA3.NDF',
 SIZE = 3MB, FILEGROWTH = 3MB, MAXSIZE = UNLIMITED)
LOG ON
(NAME='SKG_TEST2_LOG', FILENAME='E:\SQL SERVER\USERDB\LOG\SKG_TEST2_LOG.LDF',
 SIZE = 3MB, FILEGROWTH = 3MB, MAXSIZE = UNLIMITED);
GO
-- Change the Recovery Model to SIMPLE
USE [MASTER];
GO
ALTER DATABASE [SKG_TEST2] SET RECOVERY SIMPLE;
GO

-- Create a Sample Table
USE [SKG_TEST2];
GO
CREATE TABLE [DBO].[DEMOTBL1] (
DEMOTBLID		INT IDENTITY(1,1),
DEMONAME		CHAR(20)  
);
GO
-- INSERT BASE DATA INTO THE TABLE 
USE [SKG_TEST2];
GO
INSERT INTO [DBO].[DEMOTBL1] 
VALUES ('Sudeepta Ganguly'), ('Vikas Sinha'), ('Manas Dash');
GO 

-- LIST ALL THE INSERTED ROWS
USE [SKG_TEST2];
GO
SELECT * FROM [DBO].[DEMOTBL1];
GO

-- Create a Snapshot of the Database SKG_TEST2
USE [MASTER];
GO
CREATE DATABASE [SKG_TEST2_SS] ON
(NAME='SKG_TEST2', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST2.SS'),
(NAME='SKG_TEST2_DATA2', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST2_DATA2.SS'),
(NAME='SKG_TEST2_DATA3', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST2_DATA3.SS')
AS SNAPSHOT OF [SKG_TEST2];
GO
-- List all the data from the table in the Snapshot
USE [SKG_TEST2_SS];
GO
SELECT * FROM [DBO].[DEMOTBL1];
GO

-- List all the rows from the Source Database & Snapshot
USE [master];
GO
SELECT * FROM [SKG_TEST2].[DBO].[DEMOTBL1];
SELECT * FROM [SKG_TEST2_SS].[DBO].[DEMOTBL1];
GO

-- Updata a row in the Source Database Table
USE [SKG_TEST2];
GO
UPDATE [DBO].[DEMOTBL1] 
	SET DEMONAME = 'Manas R. Dash' WHERE DEMOTBLID = 3;
GO

-- List the row with demotblid = 3 from the Source Database & Snapshot
USE [master];
GO
SELECT * FROM [SKG_TEST2].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] = 3;
SELECT * FROM [SKG_TEST2_SS].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] = 3;
GO

-- Updata the same row once again in the Source Database Table
USE [SKG_TEST2];
GO
UPDATE [DBO].[DEMOTBL1] 
	SET DEMONAME = 'Manas Ranjan Dash' WHERE DEMOTBLID = 3;
GO

-- List the row with demotblid = 3 from the Source Database & Snapshot
USE [master];
GO
SELECT * FROM [SKG_TEST2].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] = 3;
SELECT * FROM [SKG_TEST2_SS].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] = 3;
GO

-- Insert two new row to the Source Database Table
USE [SKG_TEST2];
GO
INSERT INTO [DBO].[DEMOTBL1] 
VALUES ('Vinod Kumar'), ('Pinal Dave');
GO 

-- List two rows with demotblid > 3 from the Source Database & Snapshot
USE [master];
GO
SELECT * FROM [SKG_TEST2].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] > 3;
SELECT * FROM [SKG_TEST2_SS].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] > 3;
GO

-- Updated a newly entered row
USE [SKG_TEST2];
GO
UPDATE [DBO].[DEMOTBL1]
	SET [DEMONAME] = 'Vinod Kumar M' WHERE [DEMOTBLID] = 4;
GO

-- List two rows with demotblid = 4 from the Source Database & Snapshot
USE [master];
GO
SELECT * FROM [SKG_TEST2].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] = 4;
SELECT * FROM [SKG_TEST2_SS].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] = 4;
GO

-- Create another snapshot
-- Create a Snapshot of the Database SKG_TEST2
USE [MASTER];
GO
CREATE DATABASE [SKG_TEST3_SS] ON
(NAME='SKG_TEST2', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST3.SS'),
(NAME='SKG_TEST2_DATA2', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST3_DATA2.SS'),
(NAME='SKG_TEST2_DATA3', FILENAME='E:\SQL SERVER\USERDB\DATA\SKG_TEST3_DATA3.SS')
AS SNAPSHOT OF [SKG_TEST2];
GO

-- Delete two rows from the Source Database Table 
-- (one newly inserted row and one previously inserted row)
USE [master];
GO
DELETE FROM [SKG_TEST2].[DBO].[DEMOTBL1]
	WHERE [DEMOTBLID] IN (2, 5);
GO

-- List two rows (which are deleted) from Source Database & Snapshot
USE [master];
GO
SELECT * FROM [SKG_TEST2].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] IN (2,5);
SELECT * FROM [SKG_TEST2_SS].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] IN (2,5);
SELECT * FROM [SKG_TEST3_SS].[DBO].[DEMOTBL1] WHERE [DEMOTBLID] IN (2,5);
GO

-- Revert back from Snapshot 
USE [master];
GO
RESTORE DATABASE [SKG_TEST2]
	FROM DATABASE_SNAPSHOT = 'SKG_TEST2_SS';
GO
-- Msg 3137, Level 16, State 4, Line 1
-- Database cannot be reverted. Either the primary or the snapshot names are improperly specified, 
-- all other snapshots have not been dropped, or there are missing files.

-- Drop all the Snapshot, except the one, from where you want to revert back
USE [master];
GO
DROP DATABASE [SKG_TEST3_SS];
GO

-- Revert back from the Snapshot
USE [master];
GO
RESTORE DATABASE [SKG_TEST2]
	FROM DATABASE_SNAPSHOT = 'SKG_TEST2_SS';
GO

-- List all the rows from the Source Database & Snapshot
USE [master];
GO
SELECT * FROM [SKG_TEST2].[DBO].[DEMOTBL1];
SELECT * FROM [SKG_TEST2_SS].[DBO].[DEMOTBL1];
GO

-- Drop the snapshot
USE [master];
GO
DROP DATABASE [SKG_TEST2_SS];
GO

-- Cleanup
USE [master];
GO
DROP DATABASE [SKG_TEST2];
GO

Happy Learning Smile

About these ads

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in DatabaseSnapshot, How To, SQL Server, TestLab and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s