SQL Server Database Snapshots

A SQL Server database snapshot is a read-only copy of a database at a specific point in time that can be created on the same server as the original database (local only). Snapshots are primarily used for reporting purposes and test databases but can also act as a quasi-backup of your original database. But a snapshot is NOT a replacement for your backup strategy as you cannot revert to a snapshot if your primary database is unavailable. So snapshots act as a backup but only in limited circumstances. Snapshots have a .ss file extension.

Snapshots Start Small and Grow In Size As Time Goes By

Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file.

T-SQL statements to create a snapshot:

EXAMPLE 1

This example creates a database snapshot on the AdventureWorks2008R2 database. The snapshot name, AdventureWorks2008R2_dbss_1800, and the file name of its sparse file, AdventureWorks2008R2_data_1800.ss, indicate the creation time, 6 P.M (1800 hours).

CREATE DATABASE AdventureWorks2008R2_dbss1800 ON

( NAME = AdventureWorks2008R2_Data, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\AdventureWorks2008R2_data_1800.ss' )

AS SNAPSHOT OF AdventureWorks2008R2;

EXAMPLE 2 - Snapshot of a database that has filegroups

This example creates a database snapshot, sales_snapshot1200, on the Sales database.

--Creating sales_snapshot1200 as snapshot of the

--Sales database:

CREATE DATABASE sales_snapshot1200 ON

( NAME = SPri1_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SPri1dat_1200.ss'),

( NAME = SPri2_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SPri2dt_1200.ss'),

( NAME = SGrp1Fi1_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\mssql\data\SG1Fi1dt_1200.ss'),

( NAME = SGrp1Fi2_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SG1Fi2dt_1200.ss'),

( NAME = SGrp2Fi1_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SG2Fi1dt_1200.ss'),

( NAME = SGrp2Fi2_dat, FILENAME =

'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\SG2Fi2dt_1200.ss')

AS SNAPSHOT OF Sales