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.
with
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\TEST02.mdf.ss')
,(name=ds2, filename='J:\SQLData\TEST02_1.ndf.ss')
,(name=ds3, filename='J:\SQLData\TEST02_2.ndf.ss')
,(name=ds4, filename='J:\SQLData\TEST02_4.ndf.ss')
as snapshot of Post_Release
GO