Creating a Database Snapshot
This topic describes some best practices for creating database snapshots and provides a link to the procedure for creating them.
Any user who can create a database can create a database snapshot.
Important
Database snapshots, introduced in SQL Server 2005, are available only in the Enterprise editions of SQL Server 2005 and SQL Server 2008.
Best Practices for Creating Database Snapshots
Following are some best practices for naming database snapshots, timing when you create them, limiting their number, and redirecting client connections to a snapshot.
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 AdventureWorks 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:
AdventureWorks_snapshot_0600
AdventureWorks_snapshot_1200
AdventureWorks_snapshot_1800
Alternatively, if the creation time of these daily snapshots varies from day to day, a less precise naming convention might be preferable, for example:
AdventureWorks_snapshot_morning
AdventureWorks_snapshot_noon
AdventureWorks_snapshot_evening
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
If you want to revert to a database snapshot, you need to delete any other snapshots from that database.
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.
To create a database snapshot
Note
SQL Server Management Studio does not support the creation of database snapshots.