Tag Archives: Database Snapshot

Steps to restore Database using Database Snapshot

Problem: Take a scenario where vendor wants you to up-grade the vendor databae using the SQL scripts. In case if something goes wrong you can either recover database by restoring all backup or by creating database snapshot.

Database snapshots feature is available from the SQL Server 2005. Snapshot will be used to create a read-only copy of a database at a given point in time. Any transactions which are uncommitted at the time you create a snapshot will not be included in the database snapshot. You can create multiple snapshots of a source database, but those snapshots must reside on the same instance as the source database.

You must have SQL Server enterprise edition to create the snapshot. A database snapshot only contains the data pages that have changed in the source database since the snapshot was created. It contains the original copies of those pages in order to give the effect of a read-only view. The file that is created to hold the changed data pages when the snapshot is created is known as a sparse file.

A source database that contains a snapshot cannot be dropped, detached, or restored until all of the snapshots have been dropped.

Check below queries to restore the database using snapshot
Before you start restoring using database snapshot
— Make sure the source database does not contain read-only or compressed filegroups.
–Make sure all the files are online that were online when the snapshot was created.
–Delete all snapshots of the source database, except the one you are reverting to.

create database db_pool
use db_pool

create table tb1
	id int,
	name varchar(10)

insert into tb1 values(10,'Jugal')
go 50;

--select the database and execute the below query to get logical name
SELECT name FROM sys.database_files WHERE type <> 1

--execute below command to create the database snapshot
CREATE DATABASE db_pool_Snapshot
(Name = db_pool,
 FileName = 'C:\db_pool_Data.ss')
--select the original database and drop the rows
use db_pool
delete from tb1

--use the snapshot and query tb1, we can see the data evenif it is deleted 
 use db_pool_Snapshot
 select * from tb1
 --now restore database using below script from snapshot
USE master
 --query the database to check changes
 use db_pool
 select * from tb1

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


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.





NAME = AdventureWorks_Data,

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


AS SNAPSHOT OF AdventureWorks