Home > Database Snapshot > Database SnapShot SQLServer 2008

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.

Categories: Database Snapshot
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: