Datbase Snapshot

What is database snapshot?
A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation, minus any uncommitted transactions. Uncommitted transactions are rolled back in a newly created database snapshot because the Database Engine runs recovery after the snapshot has been created. 

 

Is database snapshot transitionally consistent?
Yes.
Each database snapshot is transitionally consistent with the source database at the moment of the snapshot's creation. When we create a database snapshot, the source database will typically have number open transactions. Before the snapshot becomes available, the open transactions are rolled back to make the database snapshot transitionally consistent. Just like it follows the recovery interval step and it will not affect the source database. 

 

What are the uses of database snapshot?
Reporting Purpose
we can recover damaged database using database snapshot
also useful if we are planning to do major change in the source database
Mirroring database is always in recovering mode, to read that database we can use database snapshot.
Which edition of SQL Server 2005 supports database snapshot? Enterprise Edition 

 

Is Developer edition of SQL Server 2005 supports database snapshot?
No. 

 

What is copy-on-write operation in database snapshot?
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.

 

What is Sparse file in Database Snapshot?

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. 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.

 

What is NTFS File System?

The NTFS acronym stands for New Technology File System. The name derives from the implementation of very innovative data storage techniques that were refined in NTFS. While none of the techniques are unique to NTFS, it is the first time that so many innovations were released at once on a production file system. The FAT file system had long been criticized for not including some of the more obvious improvements such as journaling, disk quotas, and file compression. However, these improvements made NTFS incompatible with previous versions of Windows, and also with hard disk tools designed for FAT file systems. For example, data recovery tools such as GetDataBack and partitioning tools such as PartitionMagic would run on Windows NT, yet could not function on the newer file system. This led to much frustration with users who had purchased licenses for these products before upgrading to Windows NT.

 

Explain Sparse file Size in database snapshot

At the creation time sparse file will take very little space, but as the data changes occurred into the parent database, data page before the changes copied into the sparse file. Thus sparse file grows. Sparse files are the feature of NTFS file system. As the sparse file grows NTFS will allocate the space to sparse file gradually.

 

Why does the size of a sparse file slightly exceed than the space actually filled by pages in it?

Sparse files grow in 64-kilobyte (KB) increments; thus, the size of a sparse file on disk is always a multiple of 64 KB. The latest 64-KB increment holds from one to eight 8-KB pages, depending on how many pages have been copied from the source database. This means that, on the average, the size of a sparse file slightly exceeds the space actually filled by pages.

 

How to create database snapshot?

1. Based on the current size of the source database, ensure that you have sufficient disk space to hold the database snapshot. The maximum size of a database snapshot is the size of the source database at snapshot creation.


2. Issue a CREATE DATABASE statement on the files using the AS SNAPSHOT OF clause. Creating a snapshot requires specifying the logical name of every database file of the source database.

 

Syntax

CREATE DATABASE AdventureWorks_SS ON

(

NAME = AdventureWorks_Data,

FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.ss'

)

AS SNAPSHOT OF AdventureWorks