MSSQL2005 new features Create a database snapshot

Database snapshots are a new feature of MSSQL2005 and are available only in Microsoft SQL Server Enterprise Edition. and SQL Server Management Studio does not support creating database snapshots, the only way to create snapshots is by using Transact-SQL.

A database snapshot is a read-only static view of a database, called the source database. At creation time, each database snapshot is consistent with the source database on the transaction. When you create a database snapshot, the source database usually has open transactions. Before the snapshot can be used, the open transaction is rolled back to make the database snapshot consistent on the transaction.

The client can query the database snapshot, which is useful for writing reports based on the data that was created at the time of the snapshot. Also, if the source database is corrupted later, the source database can be restored to its state when the snapshot was created.

To create a database snapshot:

• Maintain historical data to generate reports. You can access data at a specific point in time through a snapshot. For example, you can create a database snapshot at the end of a given time period (for example, fiscal quarter) to make a report later. You can then run the report that was created at the end of the period on the snapshot.

• Implement a query on a snapshot of a database to free resources on the principal database.

• Speed recovery operations, and using snapshots to restore the database to the snapshot when it was generated is much faster than restoring from backup; however, you cannot roll forward operations on the data thereafter. Depending on the disk resource, 6 to 12 scrolling snapshots can be created every 24 hours. Every new snapshot is created, and the oldest snapshot is deleted. If you want to recover, you can restore the database to a snapshot at the moment before the error occurred. Alternatively, you can manually recreate the deleted table or other missing data by taking advantage of the information in the snapshot. For example, you can bulk copy data from a snapshot into a database, and then manually merge the data back into the database.

However, as long as there is a database snapshot, the source database for the snapshot has the following limitations:

• Database snapshots must be created on the same server instance as the source database.

· The database snapshot captures the point in time when the snapshot was created, eliminating all uncommitted transactions. Uncommitted transactions are rolled back during the creation of a database snapshot because the database engine performs a recovery operation on the snapshot (transactions in the database are not affected).

• When a page that is updated in the source database is forced into a snapshot, if the snapshot runs out of disk space or encounters some errors, the snapshot becomes a suspect snapshot and must be deleted. For more information, see Deleting a database snapshot.

• Snapshot is read-only.

· Prevents snapshots of the model database, master database, and the tempdb database from being created.

· You cannot change any of the specifications for a database snapshot file.

• Files cannot be deleted from the snapshot.

• Snapshots cannot be backed up or restored.

• Snapshots cannot be attached or detached.

• Snapshots cannot be created in the FAT32 file system or RAW partition.

· Database snapshots do not support full-text indexing, and Full-text catalogs cannot be propagated from the source database.

• A database snapshot inherits the security constraints of its source database when the snapshot was created. Because the snapshot is read-only, the inherited permissions cannot be changed, and the change permissions on the source database are not reflected in the existing snapshot.

• Snapshots always reflect the filegroup status when the snapshot was created: Online filegroups remain online and offline filegroups remain offline. For more information, see "Database Snapshots with offline filegroups" later in this topic.

• If the source database has a status of Recovery_pending, it may not be able to access its database snapshots. However, once the problem with the source database is resolved, the snapshot becomes an available snapshot again.

* Read-only filegroups and compressed filegroups do not support recovery. Attempts to revert to both types of filegroups will fail. For more information about recovery, see recovering to a database snapshot.