What is database snapshot and what is it commonly used for?

 Problem: What is database snapshot and what is it commonly used for?

Solution: Database Snapshots (SQL Server)– Enterprise Edition of SQL Server is Required to have this feature.

Database Snapshot is read only database version of a SQL Server Database which acts a source database. Database snapshot captures the data page changes from a source database. The Snapshot will be created on the same server where the source database is located.  

Multiple snapshots can exist on a given source database. Each database snapshot persists until it is explicitly dropped by the database owner.

Initially there will be a sparse file, an empty file that stores the pages of the data changes in the database. So, there will a disk space consumption twice as the snapshot is on the same server.

Please see the below image to see how snapshot is filling the data pages based on the changes that occur in the database.

Update pattern A reflects an environment in which only 30 percent of the original pages are updated during the life of the snapshot. Update pattern B reflects an environment in which 80 percent of the original pages are updated during the life of the snapshot.

Pros:

1. Quick Back and Restore
2. Changes are captured by pages

Cons:

1. May Break Log sequence
2. Enterprise Edition only

Commonly Used for:

1. Reporting purposes.

2. Reverting Errors to snapshot.

1. 
   

In the later articles, I will show we can create a database snapshot and pre-requisites required.