Database snapshots

Day -9

Database snapshots

A database snapshot is a static, read-only copy of an existing Microsoft SQL Server database.The existing database is referred to as the source database when discussing database snapshots.When a snapshot is created, it is an exact read-only replica of the source database at that point in time. As the source database changes, the snapshot will be updated to ensure that it is synchronized. When creating a database snapshot, you must ensure that your source database constantly and con­sistently remains available.

1.      Database snapshots are supported only in the Enterprise version of SQL Server 2012.

2.      The source and the snapshot database must reside on the same SQL Server instance.

3.      The source database cannot be dropped, detached, or restored.

4.      Source database files cannot be dropped.

5.      Performance could be negatively affected due to increased I/O on the source.

List of the common snapshot limitations

1.      The snapshots must reside on the same server as the source database.

2.      Snapshots cannot be backed up, restored, or detached.

3.      Changes in the source database will cause the snapshot database to grow. Therefore, you should ensure that you have disk space available equal to the size of your source database.

4.      If a snapshot runs out of space, it must be deleted and re-created.

View a database snapshot with SSMS

1.     Open SSMS and connect to a server.

2.     In Object Explorer, expand the Databases folder.

3.     Expand the Database Snapshots folder.

Dropping database snapshots

At some point, you may decide that a database snapshot is no longer useful, or you may decide that it is consuming too much space and you would like to start with a fresh snapshot.

Drop a database snapshot using SSMS

1.     Open SSMS and connect to a server.

2.     In Object Explorer, expand the Databases folder.

3.     Expand the Database Snapshots folder.

4.     Right-click the database snapshot that you want to drop, and select Delete from the context menu.

5.     Check the box labeled Close Existing Connections

Reverting to a database snapshot

One of the biggest advantages you can leverage by creating database snapshots is that you may be able to use them as a backup to a database backup. Restoring from a database snapshot is a viable option in some cases, but it is not a complete copy of the database. Therefore, if the source database becomes corrupt, you cannot revert to the snap­shot.

1.     The source database must have only one snapshot.

2.     If any of the files are read-only or offline, you cannot revert to a snapshot.

3.     Any changes that occurred after the reverted snapshot was taken will be lost.

Thanks & Regards

Srikanth Goud- Virtualization Administrator