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.”