Introduction to Database Snapshots in SQL Server 2005

Database Snapshots is a new feature introduced in SQL Server 2005. It provides a mechanism that enables us to save an exact read only copy of a database when the snapshot was taken. It would be highly beneficial that a database snapshot is taken when certain bulk changes to the table data is made or you need to maintain an exact copy of your database as it was at the end of the day, each day.

Some points to be noted while working with Database Snapshots.

Database snapshot can be created using T-SQL script only, it cannot be implemented using the Management Studio.

  • Database snapshot of a database should exist on the same SQL Instance as the Source Database. Why this is so would be beyond the point of discussion for this article, so I would explain in another blog soon.
  • Database snapshots cannot be backed up. (But in the Object Explorer you will see that the menu options are available to backup)
  • Database snapshot cannot be updated or modified (That is why it is referred as read only Copy)
  • It is available only in Enterprise Edition of SQL Server 2005.

Script to create the Source Database

USE [master]
GO

CREATE DATABASE [Snapshot_Source] ON PRIMARY

( NAME = N'Snapshot_Source_Data', FILENAME =
N'D:\Snapshot_Source_Data.mdf' , SIZE = 4096KB,

MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Snapshot_Source_log',
FILENAME = N'D:\Snapshot_Source_Log.ldf' ,

SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

Creating table called DemoTable using the following script

CREATE TABLE [Snapshot_Source].[dbo].[DemoTable](

[Name] [varchar](50) NULL,

[Age] [int] NULL )

ON [PRIMARY]

Inserting records in the DemoTable

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'A' , 30 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'B' , 35 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'C' , 37 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'D' , 40 )

Run the following script in the Query Editor

SELECT * FROM [Snapshot_Source].[dbo].[DemoTable]

CREATE DATABASE SNAPSHOT_01OCT2009_6AM ON PRIMARY

( NAME = N'Snapshot_Source_Data',

FILENAME = N'D:\SNAPSHOT_01OCT2009_6AM.ss' )

AS SNAPSHOT OF Snapshot_Source;

GO

Modifying the Data in the Source Database

Let’s modify the data in the source database by the following update statements.

UPDATE [Snapshot_Source].[dbo].[DemoTable]

SET [AGE] = 50 WHERE [Name] = 'A'

UPDATE [Snapshot_Source].[dbo].[DemoTable]

SET [AGE] = 55 WHERE [Name] = 'B'

Verifying the data at the source database and also from the snapshot by running the following script in the Query Editor.

SELECT * FROM [SNAPSHOT_01OCT2009_6AM].[dbo].[DemoTable]

SELECT * FROM [Snapshot_Source].[dbo].[DemoTable]

You can use a database snapshot in situations which needs a continuous read only copy of the database at a point of time. For example, you have a reporting application which requires data to be available and it is ok to have the data as it was at the end of day yesterday. A scheduled database snapshot of your transaction based Database which runs after the working hours each day would be a good solution.

Dropping a Database Snapshot

Dropping a database snapshot is same as dropping a database, using the Drop Database command.

The following command would delete the database snapshot that was used in this example.

DROP DATABASE SNAPSHOT_01OCT2009_6AM

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty

Leave a Reply