In the article, we’ll walk through the concepts to understand database snapshots, and their benefits and limitations. This article will help you decide when to use a database snapshot, and when to use a backup. In some cases, the database relying on database backup and restore operation is not a viable option. Let’s dig into the concepts of database snapshots and see how it differs from a database backup.

Database snapshots are like a view of a database as it was at a certain point in time. It is a read-only copy of the data and the state of the pages, which are made possible using a pointer file called the sparse file. A snapshot contains the original version of pages, and changes in the source since the snapshot were created. However, remember that this is not a full copy of the database.

Snapshots are typically used for purposes of audits and reporting. Another use for snapshot backups is that multiple snapshots can be created for a database, and these can be taken at different points in time. This helps with period-over-period analyses.

It is important to understand that database snapshots are directly dependent on the source database. Therefore, snapshots can never substitute your regular backup and restore strategy. For instance, if an entire database is lost, it would mean its source files are inconsistent. If the source files are unavailable, snapshots cannot refer to them, and so, snapshot restoration would be impossible.

Snapshots can be created using the CREATE DATABASE command along with the AS SNAPSHOT option. A snapshot always starts with a near-zero size. This is because a snapshot store changes to the database since the snapshot was created. As changes in the database occur, the snapshot starts to grow, and may even see significant variation in size. Therefore, it is always recommended to keep an eye on them to avoid low-disk-space alerts.

The database snapshot feature is made available in all editions starting with SQL Server 2016 SP1.

Points to note

1. SQL Server Management Studio does not provide a graphical interface for creating snapshots; the only way to create them is using TSQL commands.
2. The snapshot file name accepts arbitrary file extensions.
3. SQL Server does not support backup operations over sparse files. In other words, sparse files cannot be backed up.
4. A database snapshot appears to never change, because read operations on a database snapshot always access the original data pages, regardless of where it resides.
5. After a page has been updated, a read operation on the snapshot still accesses the original page from the source database, and only the modified pages from the sparse file, also known as the side file.
6. DBCC commands use an internal reference of database snapshots to validate the required transactional consistency of the database.
7. When we start DBCC CHECKDB, a hidden database snapshot is created. There is no control, however, over these files, since these are created as alternate streams of the files.
8. Alternate streams are not used since SQL Server 2014. And the database snapshot is created at the same location as the existing database.
9. The database snapshots don’t reserve any space; the growth of the snapshot is directly proportional to the transaction rates that occur on the source database.
10. The mechanism of reverting the database snapshot doesn’t work on an offline or a corrupted database. Also, reverting doesn’t work if any of the source files that were online when the database snapshot is created are offline during reversion.
11. A database snapshot primarily depends on the side file for each of the data files in the source database. These side files are known as sparse files. Space allocations for these sparse files are made only for the modified portion of the data in the corresponding database file. It doesn’t include an allocation for the remaining portions of the source database—only the changes.
12. The side page table stores indicator bits which represent data validity and include an in-memory bit map.
13. During a read operation, a database snapshot always accesses the original data pages, regardless of where it resides. In the example below, the data read operation is performed on pages 1 through 10. However, if pages 3, 6 and 7 are the only ones that have changed, the pages 1, 2, 4, 5, 8, 9, 10 are read from the source database, and pages 3, 6, 7 are read from the sparse files.

Continue Reading database-snapshots-vs-database-backups