Database Snapshot

Overview

• Introduced in 2005
• Database snapshot is static, read-only view of source database at the time when snapshot was created minus uncommitted transaction
• Dependent on source database
• Physically available on server where source db is there
• Will be accessible as long as source db is there
• Not same as backup
• Multiple snapshots can be created

Behind the Scene

• Operates at data page level
• Before the page of source db is modified for the first time, original page is copied to snapshot
• Creation of snapshot does not take time as well as space as it will be empty
• Gradually it will keep storing all original data pages which have been modified after taking the snapshot

Creating Database Snapshot

• *.mdf and all *.ndf files would be taken into consideration while creating database snapshot
• Extension is .ss
• Syntax

CREATE DATABASE SourceDatabase_Snapshot ON -- Snapshot Name

(

      NAME = SourceDatabase, -- Logical FileName of Original Database

      FILENAME = 'C:\SourceDatabase.ss' -- Location of File Name with .ss ext

)

AS SNAPSHOT OF SourceDatabase -- Database Name

• Now when you check size of this .ss file, it will be same as .mdf file of Original Database, but actually it will empty. When we check the free disk space in My computer of that particular drive, it won’t change after creating snapshot.

Using Database Snapshot

• After creating, snapshot will not be available in Object Explorer, however it will be available in Database dropdown in SSMS.
• We can say Use SnapshotName OR select * from SnapshotName.dbo.TableName

 Use of Database Snapshot

• History Maintenance
• Reporting purpose
• Before doing any major update, we can create a snapshot. If operation was not successful, we can revert back to original stage 

Reverting snapshot to Original Stage

• We can go back to original stage from the point of time when we took the backup.
• Limitations

1. Source db should not contain any read-only OR offline files
2. Only one snapshot should be there
3. After reverting, Transaction log backups won’t work, so full backup has to be taken

• Syntax

RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>

Drop snapshot

DROP DATABASE SalesSnapshot0600