Pros and Cons of Database Snapshot

The following are the advantages and disadvantages of database snapshot.

Pros

* The user can create as many snapshots as he/she wants quickly in no amount of time. The user can schedule to take snapshots every hour.

* The snapshots can be used in restore operations.

* The corrupted or deleted data can be recovered from the snapshot to repair the primary database.

* In case of user error, the administrator can revert back to the snapshot taken just before the error.

* When you query a snapshot, you will not experience blocking due to update/insert operations that you might have to contend with when querying the source database

Cons

* Database snapshots are available only in the SQL Server 2005 enterprise edition.

* Database snapshots are dependent on the primary database. If the primary database goes offline, then the snapshots are not accessible.

* Performance of the source database is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.

* Full-text indexing is not supported in database snapshots.

* If the data changes rapidly, the snapshot might run out of disk space. If a database snapshot runs out of space, it is marked as SUSPECT, and it must be dropped.

* You cannot grant a new user access to the data in a snapshot. Permissions are inherited form the source database as it existed at the time of snapshot creation.

* Because of the reliance on the source database, the snapshot has to exist on the same server as that source database.