DATABASE SNAPSHOTS were added to the SQL Server product in the 2005 version. They are great for saving a version of the database before major data changes happen and perfect for rolling back the changes if they are not successful. While books online suggest other uses, I think this one is the best. Please see the limitations of snapshots before creating one. I am going to explain how to create a database snapshot, how to revert back to a snapshot after corrupted occurs, and how to drop a database snapshot.
A SNAPSHOT uses the NTFS SPARSE file technology. Basically, snapshot data files are empty upon creation and grown in increments of 64 KB. When changes such as UPDATES or DELETES happen to the data, the original data is written to the snapshot before applying the change to the database. The size of the SNAPSHOT is at most the size of the database at the time the snapshot is taken. As a convention, a *.ss extension is used for snapshot data files.
The CREATE DATABASE, RESTORE DATABASE, and DROP DATABASE statements are used perform operations with SNAPSHOTS.
This article assumes the [WILDLIFE] database has been built using the scripts in either Part 1 or Part 2. We are going to create a sub-directory to hold the new snapshot data files. Snapshots are based on the original data files. The below snippet creates a SNAPSHOT of the [WILDLIFE] database.
|
1 2 3 4 5 6 7 8 9 |
-- Modify production directory to hold snapshots EXEC xp_cmdshell 'mkdir c:\MSSQL\SNAP'; GO -- Create a new snapshot database CREATE DATABASE WILDLIFE_SS ON ( NAME = WildLifeData, FILENAME = 'C:\MSSQL\SNAP\Wild_Life_Data.ss' ) AS SNAPSHOT OF [WILDLIFE]; GO |
I can not stress enough that SNAPSHOTS should not be used for point in time recovery. They are great precaution before execution a major ETL process. If the ETL process fails, the SNAPSHOT can be used to revert the database. SNAPSHOTS are quicker than a RESTORE process since they keep track of the changes only. A 1 TB database that is corrupted after 1 GB of ETL data changes has alot less data to restore than using a full recovery strategy.
The following snippet deletes odd number entries in the ANIMALS table. This is a simple example of a ETL process gone bad.
|
1 2 3 4 5 6 7 8 |
-- Select odd id numbers SELECT COUNT(*) AS ODDNUMS FROM WILDLIFE.dbo.ANIMALS WHERE (ID % 2) = 1 -- Remove the odd numbers (unwanted damage) DELETE FROM WILDLIFE.dbo.ANIMALS WHERE (ID % 2) = 1 > |
Last but not least, the SNAPSHOT can be used to revert the database to a state before the unwanted DELETE operation.
|
1 2 3 4 5 6 7 8 9 10 11 |
-- Start with master USE master; GO -- Do a restore with the snapshot (REVERT) RESTORE DATABASE [WILDLIFE] FROM DATABASE_SNAPSHOT = 'WILDLIFE_SS'; GO -- Total number of records = 445 SELECT COUNT(*) AS TOTAL FROM WILDLIFE.dbo.ANIMALS </span> |
To clean up after the ETL process, use the DROP DATABASE command to remove the SNAPSHOT.
|
1 2 3 4 5 6 7 8 |
-- Use master USE [master] GO -- Delete existing snapshot database IF EXISTS (SELECT name FROM sys.databases WHERE name = N'WILDLIFE_SS') DROP DATABASE [WILDLIFE_SS] GO |
In summary, DATABASE SNAPSHOTS can be used to prevent unwanted changes to the database during major ETL updates. They should not be used in place of normal FULL, DIFFERENTIAL, and LOG backups. Snapshots will not get you that point in time recovery that is needed by most business lines.
Enclosed is the complete sample SQL script.
