This blog will no longer be updated.

New content is available on my new blog

Generate Create Database Snapshot script - Piotr Rodak

Generate Create Database Snapshot script

This post is about yet another way of skinning a cat.

Recently I 'discovered' usefulness of database snapshots. I find them extremely useful for testing environments, where it is important to be able to revert to initial state of environment in case of any issues.

One thing that never stops amusing me though is, that SQL Server Management Studio 2005 doesn't provide simple things as 'Create snapshot' of selected database for example (There are other missing things of course!).

So, after creating a few snapshots manually, I looked for a script that creates snapshot code for me. I found script posted some time ago by Dejan Sunderic, but it wasn't what I was looking for. I don't want to have to create a stored procedure anywhere to run some adhoc queries. I decided to write something simple that I can put into templates in my SSMS and run it on any database I want. This is what I came up with:

---this script generates create snapshot code. run in text mode.


preamble(c) as (select 'create database ' + db_name() + '_Snapshot on'),

files(c) as (select '(name=' + name + ', filename=''' + physical_name + '.ss'')' + char(10) from sys.database_files where type = 0),

filescoalesce (c) as (select c + ',' from files for xml path('')),

lastline(c) as (select 'as snapshot of ' + db_name() + char(10) + char(10) + 'GO' + char(10))

select c [--] from preamble

union all

select left(c, len(c) -2) from filescoalesce

union all

select c from lastline


 The output for one of my test databases looks like this, ready for execution:

create database Post_Release_Snapshot on

(name=ds, filename='J:\SQLData\')

,(name=ds2, filename='J:\SQLData\')

,(name=ds3, filename='J:\SQLData\')

,(name=ds4, filename='J:\SQLData\')

as snapshot of Post_Release




Published 26 March 2009 16:58 by Piotr Rodak
Filed under: ,


# Rollback code releases with “Database Snapshots”

There are some great 3rd party tools to help with version control and change control for your databases

12 August 2009 12:18 by DBA Tipster

# Makesafe script - set up security for developers

I worked yesterday on a script that is supposed to run on DEV database after it has been restored from

24 October 2009 22:46 by Piotr Rodak