Best Practice: Naming Database Snapshots

Before creating snapshots, it is important to consider how to name them. Each database snapshot requires a unique database name. For administrative ease, the name of a snapshot can incorporate information that identifies the database, such as:

The name of the source database.

An indication that the new name is for a snapshot.

The creation date and time of the snapshot, a sequence number, or some other information, such as time of day, to distinguish sequential snapshots on a given database.

For example, consider a series of snapshots for the AdventureWorks2012 database. Three daily snapshots are created at 6-hour intervals between 6 A.M. and 6 P.M., based on a 24-hour clock. Each daily snapshot is kept for 24 hours before being dropped and replaced by a new snapshot of the same name. Note that each snapshot name indicates the hour, but not the day:

Best Practice: Limiting the Number of Database Snapshots

Creating a series of snapshots over time captures sequential snapshots of the source database. Each snapshot persists until it is explicitly dropped. Because each snapshot will continue to grow as original pages are updated, you may want to conserve disk space by deleting an older snapshot after creating a new snapshot.

Note! To revert to a database snapshot, you need to delete any other snapshots from that database.

Best Practice: Client Connections to a Database Snapshot

To use a database snapshot, clients need to know where to find it. Users can read from one database snapshot while another is being created or deleted. However, when you substitute a new snapshot for an existing one, you need to redirect clients to the new snapshot. Users can manually connect to a database snapshot by means of SQL Server Management Studio. However, to support a production environment, you should create a programmatic solution that transparently directs report-writing clients to the latest database snapshot of the database.

Permissions

Any user who can create a database can create a database snapshot; however, to create a snapshot of a mirror database, you must be a member of the sysadmin fixed server role.

Issue a CREATE DATABASE statement on the files using the AS SNAPSHOT OF clause. Creating a snapshot requires specifying the logical name of every database file of the source database. The syntax is as follows:

CREATE DATABASE database_snapshot_name

ON

(

NAME =logical_file_name,

FILENAME ='os_file_name'

) [ ,...n ]

AS SNAPSHOT OF source_database_name

[;]

Where source_database_name is the source database, logical_file_name is the logical name used in SQL Server when referencing the file, os_file_name is the path and file name used by the operating system when you create the file, and database_snapshot_name is the name of the snapshot to which you want to revert the database. For a full description of this syntax, see CREATE DATABASE (SQL Server Transact-SQL).

Note

When you create a database snapshot, log files, offline files, restoring files, and defunct files are not allowed in the CREATE DATABASE statement.

Examples (Transact-SQL)

A. Creating a snapshot on the AdventureWorks database

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

B. Creating a snapshot on the Sales database

This example creates a database snapshot, sales_snapshot1200, on the Sales database. This database was created in the example, "Creating a database that has filegroups," in CREATE DATABASE (SQL Server Transact-SQL).