Microsoft SQL Server 2012 Bible

Chapter 24

Database Snapshots

In This Chapter

Understanding How Database Snapshots Work

Creating, Querying, and Dropping a Database Snapshot

Rolling Back a Database Snapshot

The 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.

Note

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 ...