Microsoft® SQL Server® 2008 Bible

Chapter 45. Database Snapshots

IN THIS CHAPTER

• How database snapshots work

• Creating a database snapshot

• Using your database snapshots

• Performance considerations and best practices

The Database Snapshot feature, originally introduced in SQL Server 2005, allows for 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 they can be created, as well as the ability to create multiple database snapshots of the same source database, providing you with snapshots of the database at different times.

Note

The Database Snapshot feature is only available in Enterprise and Developer Editions of SQL Server 2008.

The Database Snapshot feature was primarily designed to do the following:

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

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

• Database snapshots cannot be created for system databases (master, model, and tempdb).

• Database snapshots can be created only on an NTFS file system.

• Database snapshots can ...