Database snapshot: a deployment’s best friend

I have a confession to make: Historically speaking, I don’t like deployments. I dread them with an unavoidable pessimism that asks “so what is THIS going to break?”

Now, I have worked with a lot of smart people who knew how to write some fabulous code. I don’t doubt that one bit. But I’ve also worked in several places where that code wasn’t rigorously tested before it was time to be deployed in Production. Maybe the business wanted a fix deployed immediately. Maybe the deployment instructions were incomplete. Maybe velociraptors invaded your workplace.

I’ve worked in places that had all kinds issues like these, and occasionally it resulted in more time spent repairing the damage than actually developing the original fix. Those are called “bad days.” They happen, but I have learned there is one thing that can be done to minimize the impact: create a database snapshot.

The database snapshot is not exactly a substitute for your regular backups, but it’s a lot faster than writing an entire backup file. Because it only tracks changes to database pages from the moment the snapshot is created, it generally involves only a small amount of data that can quickly be rolled back (well, technically “restored”) in case you discover the deployment went sideways.

One major disclaimer: database snapshots is an Enterprise-only feature. If you’re using a Standard version you can’t do this. That is, unless you are on SQL Server 2016 and have installed Service Pack 1, in which case you are good to go!

Here’s the script I use for creating database snapshots:

DECLARE
 @dbName Varchar(255)

-- Create snapshot backup from current database
SELECT @dbname = DB_NAME()

DECLARE
  @MyDay varchar(50)
, @query varchar(2000)
, @DatabaseName varchar(128)
, @snapshotName varchar(128)
, @snapDataName varchar(128)
, @snapFileName varchar(128)
, @snapFilePath varchar(128)
, @DbFileName varchar(128)
, @curDT datetime
	
, @filecnt int
, @loopcnt int
, @sqlscript varchar(5000)

-- Check for Valid Database Name before we begin
IF (select count(*) from master.sys.databases where name = @dbName) <> 1
BEGIN 
	RAISERROR('Database Name is invalid.', 11, 1) 
	RETURN 
END

-- Set up all constant variables
set @curDT = GetDate()  -- Gets todays date

-- Define the DateTimeStamp for the filename of the snapshot
SET @Myday = CONVERT(nvarchar(50), @curDT, 112) 
SET @Myday = @Myday +  Cast(DATEPART ( hh , @curDT) as varchar(5))
SET @Myday = @Myday  + Cast(DATEPART ( mi , @curDT) as varchar(5))
SET @Myday = @Myday  + Cast(DATEPART ( ss , @curDT) as varchar(5))

-- Define the beginning of the query for creating the snapshot
SET @DatabaseName =@dbName
SET @SnapshotName =@dbName + '_SS_'+ @MyDay	
SET @query ='Create database '	+  @SnapshotName + ' ON '

-- Create a temp table to store the logical and physical names for each file in the database
CREATE TABLE #tmp_dblogname (myid int identity(1,1), fileid int, logicalname varchar(100), flname varchar(200))

SELECT @sqlscript = 'select fileid,name,filename from ' + @dbname + '.sys.sysfiles where groupid <> 0'

INSERT #tmp_dblogname
EXEC(@sqlscript)
	
-- Get the count of data files for the database
SELECT @filecnt = count(*) 
FROM #tmp_dblogname
	
SET @loopcnt = 1
WHILE @loopcnt <= @filecnt BEGIN
	SELECT
	 @SnapDataName = logicalname
	 ,@DbFileName = substring(flname, 1, len(flname)- CHARINDEX('.', REVERSE(flname)))
	FROM #tmp_dblogname 
	WHERE myid = @loopcnt
		
	-- Define logical and physical names for the snapshot file(s) and add it to the query
	SET @SnapFilename = @DbFileName +'_'+ @MyDay+'.ss'
	SET @query = @query + '(Name = '
		+ @snapDataName +', FileName="'
			+@SnapFilename +'")'

	-- Only add comma if more than one physical file	
	IF @loopcnt <> @filecnt
	 SET @query = @query + ','

	SET @loopcnt = @loopcnt + 1

END
-- Finish up the query
SET @query = @query + ' AS SNAPSHOT OF '  + @databasename + ';'

EXEC(@query)

DROP TABLE #tmp_dblogname

SELECT 'USE master RESTORE DATABASE ' + @databasename + ' FROM DATABASE_SNAPSHOT=''' + @SnapshotName + '''' AS RestoreScript

If things go wrong, you can always revert with the command that resulted from the last bit of SQL at the end.

Pro Tip: don’t forget to drop your snapshot backup after you have confirmed the deployment was successful. They will continue to grow up to the size of the source database, which might cause issues with drive space. And you don’t want to run out of drive space.

Then again, if you do forget then you’re going to have a great discussion topic for answering interview questions about a great “learning experience.”

Leave a Reply

Your email address will not be published. Required fields are marked *