Recover Data Using Database Snapshots

The Database Snapshot feature was first introduced in SQL Server 2005 Enterprise Edition and it is also available in SQL Server 2005 Enterprise Edition. Database Administrators can create database snapshots of all the user databases irrespective of the database recovery model they are using. A database snapshot is basically a static, read-only copy of the source database at a given point in time. A Database snapshots can be used for reporting and for quick recovery of data. In this article you will see how to create and use the database snapshots feature.

How Database Snapshots Work
Database snapshots basically work at the data page level. Once you have created a database snapshot, SQL Server internally creates a sparse file. A sparse file is an empty file without any user data during the time of creation. The sparse file basically takes very less disk space during the time of creation. Whenever data get modified at the source database, the source data page prior to data modification is copied from the source database to the database snapshot (spares file). Thus the database snapshot preserves the data records like how it existed in the source database at the time when the snapshot was created. The space used by the snapshots increases as more and more data pages are modified at the source database.

Create SourceDB Database
First let us create a new database named SourceDB by executing the below mentioned TSQL code.

Use master
GO

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'SourceDB')
DROP DATABASE [SourceDB]
GO

CREATE DATABASE SourceDB
GO

Create Address Table in SourceDB Database
The next step will be to create an Address table within SourceDB database by executing the below TSQL code.

USE SourceDB
GO

IF OBJECT_ID (N'dbo.Address', N'U') IS NOT NULL
    DROP TABLE dbo.Address;
GO

/* Create Address Table*/
CREATE TABLE [dbo].[Address]
(
 [AddressID] [int] NOT NULL,
 [AddressLine1] [nvarchar](60) NOT NULL,
 [City] [nvarchar](30) NOT NULL,
 [PostalCode] [nvarchar](15) NOT NULL,
 [ModifiedDate] [datetime] NOT NULL,
 [RowGUID] [UNIQUEIDENTIFIER] NOT NULL
)
ON [PRIMARY]
GO

Populate Address Table
The next step will be to populate the Address table which you have created earlier by executing the below mentioned TSQL code. For this example we will be using the data which is available in Person.Address table of AdventureWorks database.

USE SourceDB
GO

/* Populate SourceDB.DBO.Address table with data from AdventureWorks.Person.Address */
INSERT INTO Address
SELECT
AddressID,
AddressLine1,
City,
PostalCode,
ModifiedDate,
RowGUID
FROM AdventureWorks.Person.Address
GO