Internals of Database Snapshot – Part 2: Snapshot and the Buffer Pool

Check out our new blog post on Internals of Database Snapshot Part-2. This blog is the joint effort of Suhas and me.

 

• Internals of Database Snapshot – Part 2: Snapshot and the Buffer Pool
  

Internals of database-snapshot

 

Check out our new blog post on Internals of Database Snapshot. This blog is the joint effort of Suhas and me.

http://blogs.msdn.com/b/suhde/archive/2011/07/31/internals-of-database-snapshot.aspx

 

Reverting Database using Database Snapshot

 

It is always on ease having a backup and recovery strategies in place but few times it also important to recover a database to appoint which doesn’t require a full database restore or in shot no hard efforts. While working with one of my previous organization I was asked to do something which actually made me interested in using Database snapshot feature.

 

Scenario: A New modules was designed and developed for finance. Once the Module will get deployed in production excel data files need to be uploaded. At the backed few SP will execute which will do processing of data and finance related data will be updated /flag (inactive).

 

I have been asked to design a recovery strategy for the Database which recovers the database before the time of deployment and without downtime. I thought on all recovery models and at last decided to user database snapshot.

Database snapshot give me a flexibility to revert back to the time when the snapshot was created. As the snapshot database is read-only we can rum comparison between OLPT database and database snapshot.

 

Combing back to the original question, let’s say Deployment has to start at 1:00 AM, will stop the customer /user access to the application and database so that no transaction should take place. Once the application and database is inaccessible to the customer run select * from sys.sysprocesses to check what all open transaction are in place other then background processes.

Create the database Snapshot.

 

CREATE DATABASE DBSO_snapshot ON ( NAME = SPri1_dat, FILENAME = ‘K:\snapshotdb\SPri1dat_1200.ss’), ( NAME = SPri2_dat, FILENAME = ‘K:\snapshotdb\SPri2dt_1200.ss’), ( NAME = SGrp1Fi1_dat, FILENAME = ‘K:\snapshotdb\SG1Fi1dt_1200.ss’), ( NAME = SGrp1Fi2_dat, FILENAME = ‘K:\snapshotdb\SG1Fi2dt_1200.ss’), ( NAME = SGrp2Fi1_dat, FILENAME = ‘K:\snapshotdb\SG2Fi1dt_1200.ss’), ( NAME = SGrp2Fi2_dat, FILENAME = ‘K:\snapshotdb\SG2Fi2dt_1200.ss’) AS SNAPSHOT OF DBSO GO

 

 

In case you think after the deployment there is some issue with the module or data and you need to revert back. Restore the snapshot which will revert the changes to the database till the point of snapshot.

 

Here is an example

Revert the database to a database snapshot (No connection should exists to the source database to apply the snapshot.)

 

 

 

Now to verify that database is reverted successfully, check the records

 

 

 

 

 

 

Database SnapShot SQLServer 2008

Database snapshot feature is available in MS-SQLServer2005 and SQLServer2008.It is supported by all recovery models of SQL Server 2005 and SQL Server 2008.

A Database snapshot is read-only, static view of a database (the source database).

A Database snapshot captures the data and committed transitions of the OLTP Database.
No uncommitted transactions are recorded in database snapshot.

Snapshot Database doesn’t work if OLTP Database is offline or detached.

Snapshots are basically created for reporting and real only purpose but can degrade system performance.

Scenarios1:

If Programmer need access to run queries on production server to see real time data u can create a Database Snapshot and provide access to them on the same.

Scenarios 2:

Database snapshot can be used for Reporting solutions.

Imp: Database Snapshot cannot be created on system databases except model database.

Imp: If Source Database is offline then snapshot database won’t work.

Code to Create Database Snapshot

Pass Database name as parameter whose snapshot is to be created

Create Procedure [dbo].[UspDbSnapShot]

            (@Databasename varchar(100))

 

AS

 

BEGIN

SET NOCOUNT ON

Declare @DbName                      varchar(100);

Declare @DbSnapshotPath          varchar(100);

Declare @DatabaseDataFile        varchar(50);

Declare @CreateDBName            varchar(100);

Declare @DateTime                    varchar(100);

Declare @Sql                              varchar(MAX);

 

Set @DbName=@Databasename

Set @DbSnapshotPath=‘D:\SQLServer 2008\SnapshotDB\’

 

Select @DatabaseDataFile= name from sys.master_files where database_id=db_id(@DbName) and file_id=1

 

 

SELECT @DateTime=LTRIM(STR(DATEPART(HOUR, GETDATE())))  + LTRIM(STR(DATEPART(MINUTE, GETDATE())))+  LTRIM(STR(DATEPART(SECOND, GETDATE())))+

              +‘_’+ LTRIM(STR(DATEPART(DAY, GETDATE())))+ LTRIM(STR(DATEPART(MONTH, GETDATE()))) +  LTRIM(STR(DATEPART(YEAR, GETDATE())))    

 

SET @CreateDBName=@DbName+‘_’+‘snapshot’+ ‘_’+@DateTime

print @CreateDBName

 

SET @Sql=‘Create Database ‘ + @CreateDBName + ‘ ON ( NAME=’+ ””+@DatabaseDataFile+”” +‘,FILENAME=’+””+@DbSnapshotPath + @CreateDBName+‘.ss’+””+

+‘)AS SNAPSHOT OF ‘+ @DbName

 

 

execute (@sql)

SET NOCOUNT OFF

END

 

The same can be scheduled in a job to create Database snapshot at time intervals.