Professional Microsoft® SQL Server® 2008 Administration

17.8. Database Snapshots

As you have probably figured out by now, the mirror database is in NORECOVERY mode, so you cannot query the mirror database. If you want to read data from the mirror database to be used for reporting, SQL Server 2008 (Enterprise Edition and Developer Edition) has a feature called Database Snapshots, first introduced in SQL Server 2005. A database snapshot is a point-in-time, read-only, static view of a database (the source database). This feature comes in handy for reading the mirror database. Multiple database snapshots can exist but they always reside on the same SQL Server instance as the database. Each database snapshot is transactionally consistent with the source database at the point in time of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner.

Using this feature, you can create a snapshot on the mirror database. Then, you can read the database snapshot as you would read any other database. The database snapshot operates at a data-page level. Before a page of the source database is modified for the first time after the database snapshot, the original page is copied from the source database to the snapshot file. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page on the source database do not affect the data contents of the snapshot. In this ...