Dynamic Snapshot Template

The purpose of this script is to create a dynamic snapshot of a given database. It is presented as a starting point to progress your learning about the way that Templates can be used to ease your administration tasks!

-- =============================================

--

A Dynamic Snapshot Template

-- =============================================

USE master

GO

-- Drop snapshot database if it already exists

IF EXISTS

(

SELECT name

FROM sys.databases

WHERE name = N'<Database_Name, sysname, Database_Name>_<Snapshot_Id,,Snapshot_ID>'

)

DROP DATABASE <Database_Name, sysname, Database_Name>_<Snapshot_Id,,Snapshot_ID>

GO

-- Create the snapshot database

USE <Database_Name, sysname, Database_Name>

GO

DECLARE @table TABLE (

name VARCHAR(20)

, fileid INT

, filename VARCHAR(200)

, filegroup VARCHAR (20)

, size VARCHAR(20)

, maxsize VARCHAR(20)

, growth VARCHAR(20)

, usage VARCHAR(10)

)

INSERT INTO @table EXEC SP_HELPFILE

DECLARE

@DynamicSQL VARCHAR(8000)

SELECT

@DynamicSQL = 'CREATE DATABASE <Database_Name, sysname,

Database_Name>_<Snapshot_Id,,Snapshot_ID>ON' + CHAR(10)

SELECT @DynamicSQL = @DynamicSQL

+ '( NAME ='

+ name

+ ', FILENAME = '

+ ''''+filename

+ '.ss'''+')'

+ + char(13)

+ ',' FROM @Table WHERE name NOT LIKE '%log%'

SELECT @DynamicSQL = LEFT(@DynamicSQL,len(@DynamicSQL)-1) + ' AS SNAPSHOT OF <Database_Name, sysname, Database_Name>'

EXECUTE (@DynamicSQL)

-- =============================================

--

A Dynamic Snapshot Template

-- =============================================

When you encounter <A,B,C> within the script

<>contains the whole section that will be replaced within the script when you choose the option shown below from the Query options

Screen clipping 1

A is the prompt for the user

B is the Data Type of the value being expected

C is the default value that will be used if a value is not provided

So given

SELECT name

FROM sys.databases

WHERE name = '<Database_Name, sysname, Database_Name>_<Snapshot_Id,,Snapshot_ID>'

There are two parameters expected.

The first request is for Database_Name which is to be of type sysname and a value of Adventureworks has been entered for it.

The second parameter expected is Snapshot_id, it will default to text type and has not yet been provided. If it is not provided then the value Snapshot_ID will be used in the script.

Screen clipping 2

If OK was to be pressed in the current state then the inserted line would look as follows:-

SELECT name

FROM sys.databases

WHERE name = 'Adventureworks_Snapshot_ID'

Note: The Data Type is not checked and is informational only, it can be left blank if required.

If alternatively the values below were entered then the same query would read

Screen clipping 3

SELECT name

FROM sys.databases

WHERE name = 'MyDB_1'

Once you understand the way the script works then have a play with it. Some of the possible extensions of the script can include

Rename the snapshot if it already exists rather than dropping it

Introduction of formal error handling

Facilitate the capacity to database default the Snapshot ID to the current date and time

Created with Microsoft Office OneNote 2003One place for all your notes