Easily Generate SQL Server Database Snapshot Create Statements

The other day, a developer came to me and requested a database backup be taken before we ran some upgrade scripts on the database provided by a vendor. This was only a test server so there wouldn’t be any traffic on the database. I thought about it for a second and figured that creating a snapshot of the database prior to the upgrade script being executed would probably be just as easy – and in the event something with the upgrade script errored out or went wrong, we could easily revert the database back to the snapshot which would be much quicker than an entire database restore.

I went to create the database snapshot and could not remember the syntax to save my life. I decided to make a simple script to help myself out in the future.

The entire script will be available down below. But I’ll show a quick example of how to get it setup. I will be using the StackOverflow database on which I have added two additional files/filegroups.

StackOverflow_files

The only two changes that NEED to be made in the script are the @Target_DB and @Snap_Desc variables. Here, I am setting @Target_DB to ‘StackOverflow’ and @Snap_Desc to ‘Pre_Database_Upgrade_’ plus a timestamp. By default, the snapshot name will be a combination of these two variables (this can be easily changed if you like – but I like descriptive names).


/*--------SET THE VARIABLES HERE-----------------*/
--target database of which you are going to take the snapshot
set @Target_DB = 'StackOverflow'

--description or purpose of the snapshot (to make the snapshot name somewhat descriptive)
set @Snap_Desc = 'Pre_Database_Upgrade_' + CONVERT(varchar,GETDATE(), 114)

--Combining @Target_DB and @Snap_Desc to create a snapshot name.
--This can be changed to whatever you wish.
set @Snap_Name = @Target_DB + '_' + @Snap_Desc + '_ss'

/*-----------------------------------------------*/

Here is the example output from the script using the input variables from above. It generates the command to create the snapshot and also provides the command to revert the database back to the snapshot (just in case).


--Snapshot Name: StackOverflow_Pre_Database_Upgrade_14:55:28:607_ss
--Number of DB Files: 3

USE master;
CREATE DATABASE [StackOverflow_Pre_Database_Upgrade_14:55:28:607_ss] ON
( NAME = [StackOverflow],
FILENAME = 'C:\2014INSTANCES\DATA\StackOverflow.ss')
,( NAME = [StackOverflow_ETL],
FILENAME = 'C:\2014INSTANCES\DATA\StackOverflow_ETL.ss')
,( NAME = [StackOverflow_ReadOnly],
FILENAME = 'C:\2014INSTANCES\DATA\StackOverflow_ReadOnly.ss')
AS SNAPSHOT of [StackOverflow];

/*--------SNAPSHOT Revert Script------------/

USE master;
-- Reverting DB [StackOverflow] to [StackOverflow_Pre_Database_Upgrade_14:55:28:607_ss]
RESTORE DATABASE [StackOverflow] from
DATABASE_SNAPSHOT = 'StackOverflow_Pre_Database_Upgrade_14:55:28:607_ss';
GO

/-------------------------------------------*/

As of now, the script places the snapshot sparse files (.ss) in the same locations as the existing database .mdf and .ndf files. This can be manually tweaked pretty easily, but I may add in some options to specify a new location for the sparse files at some point in the future. Also, this script doesn’t create the actual snapshot – it simply outputs the command needed to create the snapshot. You will still have to manually execute the output yourself.

Anyway, here is the entire script or click here for the raw code:


/*---------------------------------------------------------------------

Author:		Justin Goodwin, https://sqljgood.wordpress.com/
							https://twitter.com/sql_jgood

Date:		June 2nd, 2016

NOTES:		For now, this places the snapshot files (.ss) in the 
			same location as the original database files. The 
			command can be tweaked once it is generated if the 
			location needs to be changed.

			
  You may alter this code for your own *non-commercial* purposes. 
  You may republish altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
  OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
  LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR
  FITNESS FOR A PARTICULAR PURPOSE.

-----------------------------------------------------------------------*/

use master
go

set nocount on; --remove '(x row(s) affected)' garbage

declare @Target_DB varchar(200) 
declare @Snap_Desc varchar(100) 

declare @Snap_Name varchar(300)

declare @Snap_Script varchar(max)
declare @File_Count_Script varchar(max)
declare @File_Count int

declare @filename varchar(100)
declare @physical_name varchar(300)
declare @cursorcount int
declare @cursorscript varchar(max)


/*--------SET THE VARIABLES HERE-----------------*/
--target database of which you are going to take the snapshot
set @Target_DB = 'Database_Name'

--description or purpose of the snapshot (to make the snapshot name somewhat descriptive)
set @Snap_Desc = 'Pre_Database_Upgrade_' + CONVERT(varchar,GETDATE(), 114)

--Combining @Target_DB and @Snap_Desc to create a snapshot name. 
	--This can be changed to whatever you wish.
set @Snap_Name = @Target_DB + '_' + @Snap_Desc + '_ss'

/*-----------------------------------------------*/

--check if the @Target_DB exists on the current server
IF NOT EXISTS(select 1 from sys.databases where name = @Target_DB)
	Begin
	Print '@Target_DB ' + @Target_DB + ' does not exist on the server! Double Check the @Target_DB name.'
	GOTO FINISH
	End


print '--Snapshot Name: ' + @Snap_Name


--create 2 temp holding tables
	if OBJECT_ID(N'tempdb..#tempcount') is not null
	begin
		drop table #tempcount
	end
	
	create table #tempcount (count int)
	
	if OBJECT_ID(N'tempdb..#tempcursor') is not null
	begin
		drop table #tempcursor
	end
	
	create table #tempcursor (
		name sysname
		,physical_name nvarchar(260)
		)

--determining how many DB data files exist for @Target_DB
set @File_Count_Script = '
select COUNT(name)
from ' + QUOTENAME(@Target_DB) + '.sys.database_files
where type = 0 --Rows only
'
--by doing this insert..exec into a temp table, we can avoid having to be connected to the target_db
insert into #tempcount
exec (@File_Count_Script)

select @File_Count = count
from #tempcount

print '--Number of DB Files: ' + CAST(@File_Count as varchar(3)) + '

'

set @cursorcount = 1 --the iterative loop counter

--begin creation of the create snapshot script here...
set @Snap_Script = 'USE master; 
CREATE DATABASE ' + QUOTENAME(@Snap_Name) + ' ON 
 '

--if there is more than 1 database data file, we will need to iterate through each file....cursor time.
set @cursorscript = '
select name
	,physical_name
from ' + QUOTENAME(@Target_DB) + '.sys.database_files
where type = 0 --Rows only
'

--more insert..exec...
insert into #tempcursor
exec (@cursorscript)

declare file_name_cursor cursor
for
select name
	,physical_name
from #tempcursor

--start cursor
open file_name_cursor

fetch next
from file_name_cursor
into @filename
	,@physical_name

while @@fetch_status = 0
begin
	if (@cursorcount > 1) --we need a leading comma for each new file after the first file
	begin
		set @Snap_Script = @Snap_Script + '
,'
	end
	--add each DB data file to the snapshot command being built
	set @Snap_Script = @Snap_Script + '( NAME = ' + QUOTENAME(@filename) + ', 
		FILENAME = ''' + REPLACE((REPLACE(@physical_name, '.mdf', '.ss')), '.ndf', '.ss') + ''')' --replace .mdf or .ndf with .ss


	set @cursorcount = @cursorcount + 1 --add to the loop counter after each data file

	fetch next
	from file_name_cursor
	into @filename
		,@physical_name
end

close file_name_cursor

deallocate file_name_cursor

--add the final piece to the snapshot create statement and build the snapshot revert statement
set @Snap_Script = @Snap_Script + '
AS SNAPSHOT of ' + QUOTENAME(@Target_DB) + ';



/*--------SNAPSHOT Revert Script------------/

USE master;  
-- Reverting DB '+QUOTENAME(@Target_DB)+' to '+QUOTENAME(@Snap_Name)+'  
RESTORE DATABASE '+QUOTENAME(@Target_DB)+' from   
DATABASE_SNAPSHOT = '''+@Snap_Name+''';  
GO

/-------------------------------------------*/

'
--output the commands that we have built
print @Snap_Script

--cleanup temp tables
drop table #tempcount
drop table #tempcursor

FINISH: --if there is an error, skip to this label

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s