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.
Leave a Reply