[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

 
Database Snapshots

 

 

Home
Analysis Services
Azure
CLR Integration
High Availability
Open Source
Security
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019
Tips
Troubleshooting
Tuning

Database Snapshots


Applies to: Microsoft SQL Server 2005 Enterprise and later.


Introduction.

This article provides a summary of the information available on Microsoft SQL Server Books Online about Database Snapshots.

 

Definition.

A snapshot is read-only, static view of a database.
 


Useful scenarios.

 

bullet Snapshots can be used in conjunction with database mirroring for reporting purposes. You can create a database snapshots on the mirror database and direct client connection requests to the most recent snapshot.
bullet Maintaining historical data for report generation. You can create a database snapshot at the end of a given time period (such as a financial period) for later reporting.
bullet Safeguarding data against administrative and user error. A database can be reverted back to the state it was in when the snapshot was created. Reverting is potentially much faster for this purpose than restoring from a backup.
bullet In a test environment, it can be useful to quickly return a database back to the state it was prior a test run.



When not to use database snapshots.
 

bullet Database Snapshots should not be seen as high availability option.
bullet Database Snapshots should not be seen as an alternative to regular backups of your data. Database snapshots can not protect your databases against disk errors or database corruption.



Some important considerations about database snapshots.
 

bullet Snapshots can be created very quickly.
bullet Available in SQL Server 2005 Enterprise Edition and later.
bullet You cannot used SQL Server Management Studio for creating a database snapshots. Snapshots have to be created using T-SQL.
bullet You cannot make a backup of a Snapshot.
bullet All recovery models support database snapshots.
bullet Snapshots are read only, static-view of a database.
bullet Only users existing in the source database at the time a snapshot is created can have access to the data in the snapshot.
bullet Performance is degraded because Snapshot add I/O overhead. Updates to the source database imply updates to the sparse files of every snapshot the source database has.
bullet Snapshots depend of the source database. Snapshots must reside on the same instance as the source database, because they share data pages as well as the same buffer cache.
bullet A database can have multiple snapshots.
bullet Every database snapshot persist until explicitly dropped.
bullet Every time you defragment the source database all pages involved in the defragment operation should be sent to the snapshot, and that will make it grow in size.
bullet Full text indexes are not available in snapshots.
bullet Snapshots tend to fragmentation at file system level.
bullet Snaphots can be marked suspect if the drive hosting the snapshot runs out of space while updating the snapshot.
bullet Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.
bullet If the source database becomes unavailable, all of its database snapshots also become unavailable.
bullet Snapshots of the model, master, and tempdb databases are prohibited.
bullet You cannot drop files from a database snapshot.
bullet You cannot attach or detach database snapshots.
bullet You cannot create database snapshots on FAT32 file system or RAW partitions.
bullet You cannot restore a backup on a database that has a snapshot created.
bullet A SQL Server instance that has a snapshot created, cannot be upgraded to a new version of SQL Server.



Planning for Database Snapshots.
 

bullet Database snapshots operate at the data-page level. 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. Therefore, it is useful to know the typical update patterns for a database when planning how much space is required during the planned lifespan of a snapshot. The maximum size to which a snapshot' sparse file can grow is the size of the corresponding source database file at the time of the snapshot creation
bullet Create a naming convention for creating snapshots that includes the creation date and time of the snapshot. Books Online suggest that a good naming convention should also include the name of the source database and an indication that the new name is for a snapshot.
bullet Database snapshots can be created only on a NTFS file system.
bullet To use a database snapshot, clients need to know where to find it. When you substitute a new snapshot for an existing one, you need to redirect clients to the new snapshot, and that should be done with some programming.
bullet Reverting breaks the log backup chain. For that reason, Microsoft recommends a full database backup after finishing the revert operation.

 

Creating a Database Snapshot.


Here is a sample database snapshot create script on the AdventureWorks database.

CREATE DATABASE AWDB_Snapshot_20080522 ON (
NAME = AdventureWorks_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWDB_Snapshot.ss')
AS SNAPSHOT OF AdventureWorks;

Open SQL Server Management Studio, and then open the Query Editor, copy/paste the above script in the Query Editor, and execute it pressing F5.






 

Make a right click over "Database Snapshots", and select "Refresh", to see the newly created database snapshot.






 

Considerations for reverting to a Database Snapshot.

 

bullet Reverting does not work in an offline or corrupted database
bullet Reverting does not work when the source database contains any read-only or compressed filegroups.
bullet Reverting does not work when any files are offline that were online when the snapshot was created.
bullet Only the snapshot that you are reverting can exist.
bullet During a revert operation, both the snapshot and the source database are unavailable.
bullet Before you can take log backups of the reverted database, you must first take a full database backup.
bullet Reverting to a snapshot drops all the full-text catalogs on the source database.



Procedure for reverting to a Database Snapshot.


1. Identify the database snapshot to which you want to revert the database using SQL Server Management Studio
2. Drop any other database snapshots.
3. Perform the revert operation. To revert the database, use the following Transact-SQL statement:

RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>

4. If the database owner changed since creation of the database snapshot, you may want to update the database owner of the reverted database.
5. Start the database.
6. Optionally, back up the reverted database, especially if it uses the full (or bulk-logged) recovery model.



Reverting to a Database Snapshot.


To revert the AdventureWorks database to the database snapshot we created before, use the following T-SQL command:

USE master;

-- Reverting AdventureWorks
-- to AWDB_Snapshot_20080522

RESTORE DATABASE AdventureWorks
FROM
     DATABASE_SNAPSHOT = 'AWDB_Snapshot_20080522';

GO

 



References.

Database Snapshots
SQL Server 2008 Books Online (October 2007)

Database Mirroring and Database Snapshots
SQL Server 2008 Books Online (October 2007)
 


 

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.