Microsoft® SQL Server® 2012 Bible

635

CHAPTER

24

Database Snapshots

IN THIS CHAPTER

Understanding How Database Snapshots Work

Creating, Querying, and Dropping a Database Snapshot

Rolling Back a Database Snapshot

T

he Database Snapshot feature, originally introduced in SQL Server 2005, enables a

point-in-time, read-only, consistent view of your user databases to use for reporting, auditing,

or recovering purposes. Before database snapshots, this functionality was achieved by running

a backup and restoring it to another database. The big advantages provided by database snapshots

are the speed at which you create them and the capability to create multiple database snapshots of

the same source database, providing you with snapshots of the database at different points

in times.

The Database Snapshot feature is an Enterprise Edition feature supported by all database recovery models.

The Database Snapshot feature was primarily designed to:

■ (^) Generate reports without blocking the production/source database.
■ (^) Perform reporting on a database mirror.
■ (^) Recover from user or administrator errors.
■ (^) Revert the source database to an earlier point in time.
■ (^) Report on historical and point-in-time data.
■ (^) Manage a test database.
Database snapshots are similar to databases in many ways but they do have some limitations of
which you should be aware:
■ (^) Database snapshots are read-only static copies of the source database.
■ (^) You cannot create database snapshots for system databases (master, model, and tempdb).
■ (^) You can create database snapshots only on NTFS file system.
c24.indd 635c24.indd 635 7/31/2012 9:25:46 AM7/31/2012 9:25:46 AM
http://www.it-ebooks.info