Dynamic Snapshot Creation Script

Recently I was testing a database release against a VLDB, and I needed the ability to revert the database to its original state, without restoring from backup. So the obvious approach here is to use SQL Server’s Database Snapshot technology, right!?

#SQLFunFact – Database snapshot creation has no GUI in SQL server and needs to be done through T-SQL. No worries, the code looks like this…

CREATE DATABASE db_snapshot_name
NAME = logical_file_name, FILENAME = ‘os_file_name’
) [ ,…n ]
AS SNAPSHOT OF source_db_name

In order to complete this script, you will need 3 things.

1) A unique name to give your snapshot which identifies it as being a snapshot, and optionally a date/time stamp to indicate when it was created.

2) A list of each logical filename from your source database, and a newly allocated physical filename. (This will be the SPARSE file that changed extents will be written to.) This can be retrieved from sys.master_files

3) You will need to decide which volume(s) will host your sparse files. Consider the following; how much data is going to change while my snapshot is active? And which drive has enough space to accommodate this new file growth? Additionally you may want to consider the contention and therefore performance effects of placing your sparse file on a drive that may host your T-Log, TempDB, or heavily used Filegroups.

Now, some databases can have a LOT of files underneath… and these can be going to any number of underlying volumes, too! You don’t necessarily want to be typing all of these out…

So with that in mind, I knocked up a script which will (for a given DB) retrieve the underlying data file information, and augment it in such a way that a ‘create database snapshot’ statement is generated. For simplicity sake, I designed it so that the sparse file will be hosted in the same volume and directory, as its NDF counterpart.

The benefit of this is that an administrator should already have an idea of what the growth patterns are on each volume, so the snapshot size against the underlying volumes would be somewhat predictable.
You will find the script below. Simply add in your DB name, set your query result output to text, and then execute against your target server.

The output from the query will be a script which you can run in another window to create your snapshot!

One of the things I did with this script was to store the dynamic query definition in a table variable instead of your typical VARCHAR (max).
By adding an identity column to our table variable definition, this gives us the ability to iterate through the dynamically generated query, effectively giving us a line number which can be referenced as a unique row.

Below is the query definition. If you have a lot of data files this may be of some use.

Alternatively, you can take the script and modify it further to suit your own environment.

/* ————— CREATE SNAPSHOT SCRIPT ————— */

DECLARE @dbname varchar(255)
SET @dbname = ‘MyDatabase’ —<<<<<< SET SOURCE DATABASE NAME HERE >>>>>>


DECLARE @SnapshotSuffix varchar(50)
DECLARE @sql table (id int identity(1,1), data varchar(max))
DECLARE @FileIDExceptionList table (id int)

SET @SnapshotSuffix = ‘_snapshot_’ + CONVERT(varchar(12),GETDATE(),112)
INSERT INTO @FileIDExceptionList (id) values (2) — default ldf id = 2 <<<<<< Add additional File Exceptions here >>>>>>

IF (SELECT (1) FROM sysfiles WHERE filename like ‘%ldf%’ and fileid not in (SELECT id FROM @FileIDExceptionList)) is not null
RAISERROR (‘you have non default MDF or LDF file extensions that will be excluded FROM this scipt, please add them to the fileID exception list.’, 16,1)

IF (SELECT name FROM sys.databases WHERE name = @dbname) IS NULL
RAISERROR (‘Cannot find the database specified. please check that it exists and try again’, 16,1)
INSERT INTO @sql values (‘use master’),(‘GO’),(‘CREATE DATABASE ‘ + @dbname + @SnapshotSuffix + ‘ ON’)
SELECT ‘( NAME = ‘ + name +’ , FILENAME = ”’ + REPLACE(physical_name, ‘.ndf’, ‘.ss’) + ”’),’ FROM sys.master_files
WHERE database_id = DB_ID(@dbname) AND physical_name like ‘%.ndf%’
SELECT ‘( NAME = ‘ + name +’ , FILENAME = ”’ + REPLACE(physical_name, ‘.mdf’, ‘.ss’) + ”’),’ FROM sys.master_files
WHERE database_id = DB_ID(@dbname) AND physical_name like ‘%.mdf%’
INSERT INTO @sql values (‘AS SNAPSHOT OF ‘ + @dbname + ‘;’),(‘GO’)

SET data = REPLACE(data,’),’,’)’) WHERE id in (SELECT MAX(id) -2 FROM @sql)
SELECT data AS ‘– Generated Script — ‘FROM @sql

/* ————— End of Script ————— */

Posted on: March 22, 2015, by : admin

Leave a Reply