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.