A topnotch site

Leave a comment

Restoring Data with Database Snapshots

Restoring Data with Database Snapshots


Snapshots use a copy-on-write operation that minimizes the drive space required for each snapshot. Unless there are changes in the original database, the snapshots will remain empty. Queries to the snapshot for unchanged records will automatically be redirected to the original database. Queries to the snapshot for modified records will show the data as it existed at the time of the snapshot’s creation. Modified records are copied from the original database before they are changed. The copy-on-write process copies data at the page level, so some unchanged records will likely end up in the snapshot.

What DBA wouldn’t appreciate a process that allows for fast and easy restoration of lost data? If the process allows you and your users to examine the information before restoring it, to use a minimal amount of drive space, and to carry out all the necessary steps with only local resources, then you have a solution that not only saves you time, but improves the options you have for supporting users and developers. 
All of these options are available when using SQL Server’s Database Snapshot functionality. When configured, it gives you access to a point-in-time, read-only view of the database and its records. Multiple snapshots can be created on the same database. Database snapshots require the original database to function, and you will be required to delete them before you can remove the original database.

How To Use Database Snapshots

Database Snapshots require the Enterprise Edition of SQL Server and can only be created using T-SQL. The steps below show you how to do this on a test server. You will need the Enterprise or Developer editions of SQL Server 2005 or 2008. To use the scripts provided, create two folders on the root of the C: drive named Database and Snapshot.

  1. Create the test database.
  2. Create a snapshot
  3. Change records in original database, which was created in step1
  4. Revert to the original version of the table
  5. Revert to the original database


Step 1 – Create the test database

Use the script in below to create a database named DB1 and a table named dbo.Contacts with five (5) records.

Create Database DB1




Create Table dbo.Contacts

(ID nchar(5) NOT NULL,

FirstName nvarchar(50),

LastName nvarchar(50),

Constraint PK_Contacts Primary Key Clustered (ID Asc))


/* Insert 5 records into the Contacts Table */

Insert DB1.dbo.Contacts Values(‘101′,’John’,’Harrison’)

Insert DB1.dbo.Contacts Values(‘102′,’Jessica’,’Forthwright’)

Insert DB1.dbo.Contacts Values(‘103′,’Earl’,’Russell’)

Insert DB1.dbo.Contacts Values(‘104′,’Stanley’,’McDonald’)

Insert DB1.dbo.Contacts Values(‘105′,’Mary’,’Kellerman’)




Step 2 – Create a snapshot

Use the below script to create a snapshot of the database. After it is created, verify that the snapshot file was created (C:\Snapshot\ Notice that no log file is needed. This is a read-only version of DB1 as it existed at the time it was created. In the Figure 2 script, notice that the NAME parameter points to the actual name of the database file and not the name of the database as specified in the AS SNAPSHOT OF parameter. Databases with multiple data files would, therefore, need multiple NAME parameters, one for each file.


/* Create a snapshot of the DB1 database */

USE master



( NAME = DB1, FILENAME = ‘C:\Snapshot\’)






Step 3 – Change records in original database, which was created in step1


Modify any record from the Contacts table in the DB1 database. Verify that the information in the DB1_Snapshot database is unchanged, using the script provided in Figure 3. The changes to DB1 have caused the original version of the data pages changed to be written to the file.


/* Verify that the information in the

snapshot and the original database are the same. */

Use DB1

Select * From DB1.dbo.Contacts

Select * From DB1_Snapshot.dbo.Contacts


/* Update the records in the Contacts table and compare

it to the Snapshot version again */

Use DB1


—-Insert dbo.Contacts



Update dbo.Contacts

set FirstName=’Jonathan’

where ID=101


Delete dbo.Contacts

that the information in the

snapshot and the original database are different. */

Select * From DB1.dbo.Contacts

Select * From DB1_Snapshot.dbo.Contacts



Step 4 – Revert to the original version of the table


You can restore individual records in a table from the snapshot. Use the script in Figure 4 to see how to fix incorrect deletions or updates.


/* Reverse deletions and updates

using information in the snapshot */

Use DB1


Insert dbo.Contacts

Select * From DB1_Snapshot.dbo.Contacts

Where ID=102


Update C

Set C.FirstName = S.FirstName

From DB1.dbo.Contacts C

Inner Join DB1_Snapshot.dbo.Contacts S


Where C.ID = 101

/* Verify that the records in DB1 and the

snapshot are the same */

Select * From DB1.dbo.Contacts

Select * From DB1_Snapshot.dbo.Contacts



Step 5 – Revert to the original database

As long as the original database is still intact and online, you can restore all changes made since the snapshot. These database snapshot restores cannot be done when more than one snapshot exists. As with a normal database restore, exclusive access to the database is needed during this operation. To test this process, use Figure 3 script to create differences between both databases, then use Figure 5 to restore the entire database from the snapshot.

– Restore the DB1 database from the snapshot

– The restore operation will fail if there are other connections to the DB1 database

Use Master


Restore Database DB1 From

Database_Snapshot = ‘DB1_Snapshot’


Select * From DB1.dbo.Contacts

Select * From DB1_Snapshot.dbo.Contacts



Caveats when Using Database Snapshots

Like any other useful DBA tool, database snapshots can be overused or misused. Here are a few precautions to keep in mind before implementing this functionality in a production environment.

Backups are still required:

There is no substitute for regular, tested backups that are stored in a remote, secure location. The database snapshot requirement that the files be stored on the same server as the original database prevents them from being used in this manner. The fact that the snapshots become obsolete if the original database is lost also limits their use for this purpose. The strength of snapshots is in their ability to provide point-in-time views of your data and ad-hoc restore options.

Beware of too many snapshots:

Although the empty state of a new snapshot file makes their size negligible, take care when creating multiple snapshots on the same database. Theoretically, a database with 10 GB of data and 24 snapshots can suddenly and unexpectedly require up to 250 GB of drive space. The ability to quickly restore a database to how it looked at a particular hour of the day is very convenient, but if this functionality is not needed, the additional resource requirements can tax a mission-critical server beyond acceptable levels. A snapshot should only be created to meet specific administrative needs and then deleted when it is no longer necessary.

Using Snapshots for Read-only Operations:

The point-in-time, read-only nature of snapshots makes them a candidate for reporting or similar solutions. Using them in this way, however, limits their use as an ad-hoc restoration solution. Before allowing others to use your snapshots for development or business solutions, consider how this will affect the options available to you when administering your databases. Other dedicated read-only solutions, like Log Shipping might be considered.


If you decide to take advantage of Database Snapshots, keep its limitations in mind. It cannot replace your backup solution, and you must carefully consider the additional resource demands of each additional snapshot. It can, however, provide a fast and simple solution for viewing point-in-time data and restoring lost information from databases. Overall, it’s a useful tool for reducing downtime and improving the availability of your databases.



Leave a comment

SQL SERVER 2008 : ” This query provide all necessary information of database”

SQL Server2000- Daily checklist for database

This query provide all necessary information of database.

create table #logsize
(Dbname varchar(200),dbstatus varchar(50),Recovery_Model varchar(40) default (‘NA’), Log_File_Size_MB decimal(20,2)default (0),
log_Space_Used_MB decimal(20,2)default (0),log_Free_Space_MB decimal(20,2)default (0))

insert into #logsize(Dbname,dbstatus,recovery_model,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
exec sp_msforeachdb
‘use [?];
select DB_NAME() AS DbName,
CONVERT(varchar(20),DatabasePropertyEx(”?”,”Status”)) ,
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ”SpaceUsed”) AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 – sum(CAST(FILEPROPERTY(name,”SpaceUsed”) AS INT))/128.0 AS log_Free_Space_MB
from sysfiles where groupid=0 group by groupid


create table #dbsize
(Dbname varchar(200), file_Size_MB decimal(20,2)default (0),Space_Used_MB decimal(20,2)default (0),
Free_Space_MB decimal(20,2) default (0))

insert into #dbsize(Dbname,file_Size_MB,Space_Used_MB,Free_Space_MB)
exec sp_msforeachdb
‘use [?];
select DB_NAME() AS DbName,

sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ”SpaceUsed”) AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 – sum(CAST(FILEPROPERTY(name,”SpaceUsed”) AS INT))/128.0 AS Free_Space_MB
from sysfiles where groupid<>0 group by groupid ‘


(file_size_mb + log_file_size_mb) as DBsize,
from #dbsize d join #logsize l
on d.Dbname=l.Dbname