SQL Server database Snapshot working principle

A database snapshot is a read-only copy of a database that is a map of all data in a database, and determines its content by the point at which the snapshot is executed. This article describes how to manipulate database snapshots through an instance.

Another powerful new feature of SQL Server 2005 is database snapshots. A database snapshot is a read-only copy of a database that is a map of all data in a database, and determines its content by the point at which the snapshot is executed.

These database snapshots are valuable in terms of reporting, because in the snapshot database or in the original database, no locks are executed for any query. Snapshots can also be used in disaster recovery because you can restore existing data to an existing snapshot, or you can store individual necessary tables and data in events declared by harmful data operations.

How does a database snapshot work?

A database snapshot can be generated using a typical database command create DB statement, with an additional description of the source database snapshot in the declaration. When the snapshot is established, a sparse file is generated at the same time. This file (which can only be used on NTFS volumes) does not have disk space assigned to it during initialization-although you may see the size of the file in Windows Explorer, it looks the same size as the original source database file. In fact, the size of this file is close to zero for disk.

The data files that the database snapshot reads at initialization are from the source database. When the data for the source database changes, the data engine copies the original data from the source database to the snapshot database. This technique ensures that the snapshot database reflects only the state of the data when the snapshot is executed. When the Select command is used to publish against a database snapshot, the data page reads whether it is positioned in the source database data file or in the snapshot database data file without a lock being published. Because no locks are published in a read-only database snapshot, database snapshots are an important solution for report solutions.

An instance of a snapshot

Now, let's take a look at how database snapshots work in SQL Server 2005. To do this, I first need a source database as the source of the snapshot. The following script creates a source database: