SQL Server 2005 - Automating Creation of Database Snapshots

Wednesday Aug 3rd 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

MAK demonstrates the creation of database snapshots and how to automate the creation of those snapshots.

SQL Server 2005 is packed with many new features. One of the new features that I would like to discuss in this article is Database Snapshots, which are read only static views of a database. SQL Server 2005 allows you to create multiple snapshots on a database. In this article, I would like to demonstrate the creation of database snapshots and automating the creation of database snapshots.

First let's simulate the whole snapshot creation process.

Step 1

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement creates the database MyDB.

Create Database MyDB on Primary
(Name ='Mydb_Data',
FileName= 'C:\data\MyDB_Data.Mdf',
Size=100MB,
MaxSize=200MB,
FILEGROWTH=10%)
Log on
(Name = 'Mydb_Log',
FileName= 'C:\data\MyDB_Log.Ldf',
Size=30MB,
MaxSize=50MB,
FILEGROWTH=10%);

Step 2

Let us create a table and insert some rows.

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement creates the table Hubble_Galaxies and inserts 5 rows to the table.

Use MyDB
go
Create table Hubble_Galaxies (Id int, name varchar(100))
go
insert into Hubble_Galaxies values (1,'MilkyWay')
insert into Hubble_Galaxies values (2,'Spiral Galacy NGC 1300')
insert into Hubble_Galaxies values (3,'Whirpool Galacy M51')
insert into Hubble_Galaxies values (4,'Galaxy NGC 1427A')
insert into Hubble_Galaxies values (5,'NGC 3370')
go

Step 3

Now let's create a snapshot for the database MyDB.

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement creates the snapshot MyDB_Snapshot1 for the database MyDB.

use master
go
Create Database MyDB_Snapshot1  on
(Name ='Mydb_Data',
FileName= 'C:\data\MyDB_Data.SS1')
AS SNAPSHOT of MyDB;
go

This would create a file C:\data\MyDB_Data.SS1 as shown in Figure 1.0


Fig 1.0

Step 4

Now let's delete some rows from the original database.

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement deletes two rows from the MyDB database.

use MyDB
go
delete from Hubble_Galaxies where id in (3,5)
go

Step 5

Now let's query the table Hubble_Galaxies from the original database MyDB and from the SnapShot view MyDB_Snapshot1.

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement displays all of the rows from the table Hubble_Galaxies from the database MyDB. [Refer Fig 1.1]

use MyDB
go
Select * from Hubble_Galaxies
go


Fig 1.1

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement displays all of the rows from the table Hubble_Galaxies, from the snapshot MyDB_Snapshot1 (the snapshot view of the database MyDB.) [Refer Fig 1.2]

use MyDB_Snapshot1
go
Select * from Hubble_Galaxies
go


Fig 1.2

Now, let's automate creation of a daily database snapshot and let's keep one week worth of snapshots.

Create a SQL Server Scheduled job [Refer Fig 1.3, 1.4, 1.5 and 1.6] to execute the following statement every night at 12:01 am.

Download the code from here.

declare @MyDay varchar(20)
declare @query varchar(1000)
declare @DatabaseName varchar(128)
declare @snapshotName varchar(128)
declare @snapDataName varchar(128)
declare @snapFileName varchar(128)
declare @snapFilePath varchar(128)
set @Myday = (Select datename(weekday,getdate()))
print 'It is ' + @MyDay
Set @DatabaseName ='MyDB'
Set @SnapDataName='MyDB_Data'
Set @SnapshotName ='MyDB_Snapshot'+'_'+@MyDay
Set @SnapFilename ='c:\data\MyDB_Data'+'_'+@MyDay+'.ss'
Print 'Snapshot name is ' +@SnapshotName 
select * from sys.databases where source_database_id =db_id(@databasename) and name = @SnapshotName  
if @@rowcount <>0
begin
set @query = 'Drop database '+ @SnapshotName 
print @query
exec(@query)
end
set @query ='Create database '
  + @SnapshotName 
  + ' on  (Name = '
  +@snapDataName +', FileName="'
  +@SnapFilename +'") AS SNAPSHOT of '  + @databasename+';'
print @query
exec(@query)


Fig 1.3


Fig 1.4


Fig 1.5


Fig 1.6

When this scheduled jobs run daily, it deletes the previous weeks database snapshot and creates a new database snapshot. [Refer Fig 1.7]


Fig 1.7

The naming convention of the database snapshot is MyDB_Snapshot_Dayoftheweek

The following SQL statement will list all of the database snapshots available on a SQL Server instance. [Refer Fig 1.8]

select * from sys.databases where source_database_id is not null


Fig 1.8

Note: By changing the naming convention and the schedule of this process, you can convert this daily database snapshot process to a weekly or monthly database snapshot process.

Conclusion

This article demonstrated the creation of a database snapshot and the automating of the creation of database snapshots. By creating a database snapshot everyday, it would be more convenient to go back to a particular day to get data, or compare or to monitor the status of the data. The database can be reverted to a database snapshot using the RESTORE command.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved