Database Snapshots

During the OttawaSQL.NET study group for the exam 70-431: Microsoft SQL Server 2005 Implementation and maintenance, I did a presentation on Database Snapshots. I thought I would post the info.

Database snapshots are point in time representations of a database. They are read only. However, they can be Queried through SELECT statements. To create a snapshot, right-click database and select Script Database To... to get the DB files set up.

Then, modify the NAME and FILENAME attributes on each filegroup, usually just changing the filename to '.ds'

Finally, add 'AS SNAPSHOT OF [DatabaseName]'

*** Fair warning, this will not work in SQL Express ***
USE [master] GO CREATE DATABASE [AdventureWorksSnapShot] ON PRIMARY (
NAME = N'AdventureWorks_Data', FILENAME = N'C:\Program
Files\Microsoft SQL Server \MSSQL.2\MSSQL\Data\AdventureWorks_Data.ds' , SIZE =
167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB ) LOG ON (
NAME = N'AdventureWorks_Log', FILENAME = N'C:\Program
Files\Microsoft SQL Server \MSSQL.2\MSSQL\Data\AdventureWorks_Log.ds' , SIZE =
2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB ) AS SNAPSHOT OF [AdventureWorks] GO
Add some data to the AdventureWorks Database to test this:
USE [AdventureWorks]
INSERT INTO [AdventureWorks].[Sales].[Currency] ( [CurrencyCode]
,[Name] ,[ModifiedDate]) VALUES ( 'SSM' , 'Steves
Currency' , GETDATE() )
Query Snapshot to verify data wasn't moved:
USE [AdventureWorksSnapShot] SELECT * FROM [AdventureWorksSnapShot].[Sales].[Currency] WHERE [Sales].[Currency].[CurrencyCode]
= 'SSM'

It is also possible to recover from a database snapshot. One option is to write a query to grab data from the snapshot and INSERT/UPDATE data in the original database.

The other option is to RESTORE the database from a snapshot.

The key thing here is any backup made after the snapshot in question will be broken, an