SQL Server Database Snapshot

A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.
Multiple snapshots can exist on a given source database. Each database snapshot persists until it is explicitly dropped by the database owner.

Feature Overview

Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. The same process is repeated for every page that is being modified for the first time. To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.

To store the copied original pages, the snapshot uses one or more sparse files. Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. The following figure illustrates the effects of two contrasting update patterns on the size of a snapshot. 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.

Benefits of Database Snapshots

• Snapshots can be used for reporting purposes.
  Clients can query a database snapshot, which makes it useful for writing reports based on the data at the time of snapshot creation.
  
• Maintaining historical data for report generation.
  A snapshot can extend user access to data from a particular point in time. For example, you can create a database snapshot at the end of a given time period (such as a financial quarter) for later reporting. You can then run end-of-period reports on the snapshot. If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizational performance.
  
• Using a mirror database that you are maintaining for availability purposes to offload reporting.
  Using database snapshots with database mirroring permits you to make the data on the mirror server accessible for reporting. Additionally, running queries on the mirror database can free up resources on the principal.
• Safeguarding data against administrative error.
  
• In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
  
  
  Limitations on Database Snapshots
  The following limitations apply to database snapshots:
  
• A database snapshot must be created and remain on the same server instance as the source database.
  
• Database snapshots always work on an entire database.
  
• Database snapshots are dependent on the source database and are not redundant storage. They do not protect against disk errors or other types of corruption. Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.
  
• When a page getting updated on the source database is pushed to a snapshot, if the snapshot runs out of disk space or encounters some other error, the snapshot becomes suspect and must be deleted.
  
• Snapshots are read-only. Since they are read only, they cannot be upgraded. Therefore, database snapshots are not expected to be viable after an upgrade.
  
• Snapshots of the model, master, and tempdb databases are prohibited.
  
• You cannot change any of the specifications of the database snapshot files.
  
• You cannot drop files from a database snapshot.
  
• You cannot back up or restore database snapshots.
  
• You cannot attach or detach database snapshots.
  
• You cannot create database snapshots on FAT32 file system or RAW partitions. The sparse files used by database snapshots are provided by the NTFS file system.
  
• Full-text indexing is not supported on database snapshots. Full-text catalogs are not propagated from the source database.
  
• A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots. 
  

Syntax to create snapshot:
Example
I have a database of around 50 GB with 2 primary .MDF files in
C:\MSSQL10\MSSQL\Data\TEST.mdf
D:\MSSQL10\MSSQL\Data\TEST2.mdf

To create a snapshot:

CREATE DATABASE TEST_SS
ON
(NAME=TEST,
FILENAME='C:\MSSQL10\MSSQL\Data\TEST_SS.ss'),
(NAME=TEST2,
FILENAME='D:\MSSQL10\MSSQL\Data\TEST_SS1.ss'
)
AS SNAPSHOT OF TEST

The snapshot was created in 1 second. At the later point of time when there is any change in the TEST.mdf or TEST2.mdf the original page files will be written in the TEST_SS.ss and TEST_SS1.ss respectively. 

How Database Snapshot Work?

Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken. To store the copied original pages, the snapshot uses one or more Initially, a sparse file is an essentially empty file that contains no user data and has not yet been allocated disk space for user data. As more and more pages are updated in the source database, the size of the file grows. When a snapshot is taken, the sparse file takes up little disk space. As the database is updated over time, however, a sparse file can grow into a very large file.
The following figure illustrates a copy-on-write operation. The light gray rectangles in the snapshot diagram represent potential space in a sparse file that is as-yet unallocated. On receiving the first update to a page in the source database, the Database Engine writes to the file and the operating system allocates space in the snapshot's sparse files and copies the original page there. The Database Engine then updates the page in the source database. The following figure illustrates such a copy-on-write operation.


Read Operations on a Database Snapshot

To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside. If the page has not yet been updated on the source database, a read operation on the snapshot reads the original page from the source database. The following figure shows a read operation on a newly created snapshot, whose sparse file accordingly contains no pages. This read operation reads only from the source database.
Read operation before 1st page copied to snapshot After a page has been updated, a read operation on the snapshot still accesses the original page, which is now stored in a sparse file. The following figure illustrates a read operation on the snapshot that accesses a page after it has been updated in the source database. The read operation reads the original page from the sparse file of the snapshot.

Effect of the Update Pattern on Database Snapshot Growth

If your source database is fairly large and you are concerned about disk space usage, at some point you should replace an old snapshot with a new snapshot. The ideal lifespan of a snapshot depends on its growth rate and the disk space that is available to its sparse files. The disk space required by a snapshot depends on how many different pages in the source database are updated during the life of the snapshot. Therefore, if updates are mostly to a small subset of pages that are updated repeatedly, the growth rate will slow over time and the snapshot space requirements will remain relatively small. In contrast, when all of the original pages are eventually updated at least once, the snapshot will grow to the size of the source database. If the disk begins to fill up, the snapshots compete with each other for disk space. If the disk drive fills up, write operations to all the snapshots will fail.

Therefore, it is useful to know the typical update patterns for a database when planning how much space is required during the planned lifespan of a snapshot. For some databases, the rate of updates may be fairly constant; for example, an inventory database might have many of its pages updated daily, making it useful to replace old snapshots daily or weekly. For other databases, the proportion of updated pages may vary during the business cycle; for example, a catalog database might be updated primarily quarterly, with only occasional updates at other times; creating snapshots just before and after each quarterly update would be a logical strategy. The pre-update snapshot would permit reverting if a significant update error occurs, and the post-update snapshot could be used for report writing during the next quarter.
The following figure illustrates the effects of two contrasting update patterns on the size of a snapshot. 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.

Metadata About Database Snapshots

For database snapshots, database metadata includes the source_database_id property, which is stored in a column of the sys.databases catalog view. For more information about this property, see sys.databases (Transact-SQL).
Generally, a database snapshot does not expose metadata of its own, but it does expose metadata from its source database.
This metadata includes, for example, the data returned by the following statement:

USE <database_snapshot> SELECT * FROM sys.database_files 

where <database_snapshot>is the name of a database snapshot.