Database Snapshot
Database snapshot is a read only copy of database at a given point in time. Snapshot resides on the same server where source database exists and multiple snapshots can be created for one source database.
When Snapshots are helpful?
Snapshots help to maintain historical data for report generation.
Protect system from user or administrative errors
Database can be reverted back to the state when the snapshot was created. Reverting database from database snapshot is much faster than the backup file.
Note: Snapshots are not a HA solution, and we cannot assume snapshots as a database backups.
Limitations
If source database is unavailable then all of its snapshots become unavailable.
Snapshots are read only and static view of database in a given point of time.
Available from SQL 2005 EE and later
Not able to create snapshot using SSMS, Snapshots can be created only by using T-SQL.
We cannot attach or detach snapshots.
Backup cannot be done at database snapshot.
Snapshot must reside on the same instance where the source database is exists because they share pages.
We can create snapshots very quickly.
Full text indexes are not available in database snapshots.
You cannot grant a new user to access the database snapshot because permissions are inherited from the source database at the time of snapshot creations, later we can’t add users to database snapshot.
Initially snapshot files are very small and they grow large if database has frequent modifications.
Snapshots cannot be created for system databases master, model, msdb and tempdb.
Database snapshot become suspect if something goes wrong with source database.
Restoring database from database snapshot will breaks the log backup chain.
No comments: