Restoring Data with Database Snapshots

Author: Neil Tucker, MCT, MCITP, MCDBA, MCTS, MCSE

Abstract

What DBA wouldn't appreciate a process that allows for fast and easy restoration of lost data, allowing you and your users to examine the information before restoring it, to use a minimal amount of drive space, and to carry out all the necessary steps with only local resources? This white paper examines how SQL Server's Database Snapshot provides you with a solution that not only saves you time, but also improves the options you have for supporting users and developers.

Sample

All of these options are available when using SQL Server's Database Snapshot functionality. When configured, it gives you access to a point-in-time, read-only view of the database and its records. Multiple snapshots can be created on the same database. Database snapshots require the original database to function, and you will be required to delete them before you can remove the original database.

Snapshots use a copy-on-write operation that minimizes the drive space required for each snapshot. Unless there are changes in the original database, the snapshots will remain empty. Queries to the snapshot for unchanged records will automatically be redirected to the original database. Queries to the snapshot for modified records will show the data as it existed at the time of the snapshot's creation. Modified records are copied from the original database before they are changed. The copy-on-write process copies data at the page level, so some unchanged records will likely end up in the snapshot.

How To Use Database Snapshots

Database Snapshots require the Enterprise Edition of SQL Server and can only be created using T-SQL. The steps below show you how to do this on a test server. You will need the Enterprise or Developer editions of SQL Server 2005 or 2008. To use the scripts provided, create two folders on the root of the C: drive named Database and Snapshot.

1. Create the test database. Use the script in Figure 1 (below) to create a database named DB1 and a table named dbo.Contacts with five (5) records.

/* Create a new database named DB1 */
Use Master
GO
IF Exists (Select Name From sys.databases Where Name = 'db1')
Drop Database DB1

GO
Create Database DB1 on Primary
( Name='DB1_Data',Filename='C:\Database\DB1.mdf')
LOG ON
( Name='DB1_log',Filename='C:\Database\DB1_log.ldf')
GO

/* Create a table named Contacts in DB1 */
USE DB1
GO

Create Table dbo.Contacts
(ID nchar(5) NOT NULL,
FirstName nvarchar(50),
LastName nvarchar(50),
Constraint PK_Contacts Primary Key Clustered (ID Asc))
GO

/* Insert 5 records into the Contacts Table */
Insert DB1.dbo.Contacts
Values('101','John','Harrison')
Insert DB1.dbo.Contacts
Values('102','Jessica','Forthwright')
Insert DB1.dbo.Contacts
Values('103','Earl','Russell')
Insert DB1.dbo.Contacts
Values('104','Stanley','McDonald')
Insert DB1.dbo.Contacts
Values('105','Mary','Kellerman')
GO

2. Create a snapshot of DB1. Use the Figure 2 script to create a snapshot of the database. After it is created, verify that the snapshot file was created (C:\Snapshot\DB1_Snapshot.ss). Notice that no log file is needed. This is a read-only version of DB1 as it existed at the time it was created. In the Figure 2 script, notice that the NAME parameter points to the actual name of the database file and not the name of the database as specified in the AS SNAPSHOT OF parameter. Databases with multiple data files would, therefore, need multiple NAME parameters, one for each file.

/* Create a snapshot of the DB1 database */
USE master
GO

CREATE DATABASE DB1_Snapshot ON
( NAME = DB1_Data, FILENAME =
'C:\Snapshot\DB1_Snapshot.ss' )
AS SNAPSHOT OF DB1;
GO

3. Change records in DB1. Modify any record from the Contacts table in the DB1 database. Verify that the information in the DB1_Snapshot database is unchanged, using the script provided in Figure 3. The changes to DB1 have caused the original version of the data pages changed to be written to the DB1_Snapshot.ss file.

/* Verify that the information in the
snapshot and the original database are the same. */
Use DB1
Select * From DB1.dbo.Contacts
Select * From DB1_Snapshot.dbo.Contacts
Go

/* Update the records in the Contacts table and compare
it to the Snapshot version again */
Use DB1
Go
--Insert dbo.Contacts
--Values('106','Chris','Andrews')
--Go
Update dbo.Contacts
set FirstName='Jonathan'
where ID=101
Go
Delete dbo.Contacts
where ID=102
Go
/* Verify that the information in the
snapshot and the original database are different. */
Select * From DB1.dbo.Contacts
Select * From DB1_Snapshot.dbo.Contacts
Go

4. Revert to the original version of the table. You can restore individual records in a table from the snapshot. Use the script in Figure 4 to see how to fix incorrect deletions or updates.

/* Reverse deletions and updates
using information in the snapshot */
Use DB1
go
Insert dbo.Contacts
Select * From DB1_Snapshot.dbo.Contacts
Where ID=102
Go
Update C
Set C.FirstName = S.FirstName
From DB1.dbo.Contacts C
Inner Join DB1_Snapshot.dbo.Contacts S
ON C.ID = S.ID
Where C.ID = 101

/* Verify that the records in DB1 and the
snapshot are the same */
Select * From DB1.dbo.Contacts
Select * From DB1_Snapshot.dbo.Contacts
Go

5. Revert to the original database. As long as the original database is still intact and online, you can restore all changes made since the snapshot. These database snapshot restores cannot be done when more than one snapshot exists. As with a normal database restore, exclusive access to the database is needed during this operation. To test this process, use Figure 3 script to create differences between both databases, then use Figure 5 to restore the entire database from the snapshot.

Related Courses