Hi
In this article, I will explain Database Snapshots in SQL Server. Database Snapshot is a feature that comes with SQL Server 2005 and is an ongoing feature in 2008, 2012, 2014, 2016, SQL Server 2017 and so on. Database Snapshot; The simplest definition is that a read-only copy of the Database or snapshot of database at any given moment.
When the snapshot process is performed, SQL Server takes the current image of the selected database but does not have the current physical data on the disk for Snapshot database.
There is a unique physical data and it is just for Production database. Uncommit transactions rollback while the snapshot is being taken. Any changes made to the snapshot database will be written to the Sparse File for Snapshot database.
This means that if anybody has changed any data in Snapshot database when anybody query any select related with same data from Snapshot , then SQL Server will read the changed data from the Sparse file and read the unmodified data from the original database.
The most beautiful portrait that summarizes this scenario is the following picture that is published by MSDN.
As shown above picture, in the 1st case, 3 out of 10 pages of the production Database is being Updated. These Updates are also written in Sparse File as shown in the 1st case. in the 2st case, 8 pages from the source database are updated and 7 pages are written to the sparse file.
When a Report or Query is executed from Snapshot, SQL Server reads unchanged pages from SQL Server Source Database, and SQL Server reads changed pages from Sparse file.
The main purpose of Snapshot is to create a Stable Report or test database for developers for a certain interval by making a backup of any of the database and creating reports via Snapshot. if minor errors occur in production database, Admin can return to its original state from Snapshot.
However, it is very important that Snapshot database is connected to one-to-one its source database If it is deleted or migrated then main database can not be returned from Snapshot database. That’s why the Snapshot database isn’t exactly a Backup Strategy for SQL Server. It can be used for test databases.
I will continue to tell about SQL Server database snapshot in next post.
Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.
https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/