Off-Box Backups and Luke-Warm Standby Servers, Part III

Until now, my previous two posts have largely just been a review of best practices for making backups redundant – with only a single mention about RESTORE VERIFYONLY’s LOADHISTORY argument as a means of providing a rationale for enabling luke-warm failover servers from those backups.

And, again, just for the record: there ARE better High Availability solutions out there for SQL Server (such as Mirroring, Log Shipping, SQL Server 2012’s AlwaysOn happiness, clustering, and --in some cases -- replication). As such, the point of these posts is really just to cover options for less-expensive failover options for organizations that don’t need ‘full-blown’ failover options and as a means of describing some additional ways to make ‘full-blown’ HA solutions more redundant and capable of recovery – as you never know when you’ll need some sort of Remote-Availability or smoke-and-rubble contingency. That, as I’ve said over and over again, high-availability isn’t the same as disaster recovery.

So, with all of that said, the big problem at this point is that just having copies of your backups in secondary/remote locations is NOT a disaster recovery plan. Yes, having copies of your backups off-site is better than nothing – but merely having copies of your backups off-site isn’t going to help much in an emergency – especially if we’re talking about multiple databases of any decent size. That, and unless you’re regularly testing these secondary/luke-warm backups, you not only have any idea whether they’re viable or not, but you don’t accurately know if they’re capable of meeting RPOs and RTOs.

To that end, you need what I like to call a ‘mass recovery’ script.

The Benefits of a Mass Recovery Script

In my mind there are two primary benefits of a 'mass recovery' script. First and foremost, my experience is that most DBAs aren’t as familiar with all of the ins and outs of restoring databases in an emergency as they need to be. Or, as I like to tell many of my clients: “The LAST thing you want to be doing in a disaster scenario (with your boss standing over your shoulder) is be figuring out how to properly restore a number of databases while management is worried about when people, systems, and expensive activities can get back online.”

Second, even if you’re a ninja at restoring databases from scratch (where you’ll have to use T-SQL against raw files – unless you’ve done something with RESTORE VERIFYONLY’s LOADHISTORY functionality), you’ll still typically need to wire up the restoration of a decent number of log-file backups – which can quickly become tedious and subject to error. Especially if you have multiple databases that go down. (For example, assume that you’re making FULL backups every Sunday Night, and then DIFFERENTIAL backups every week-night at 3AM, and your database goes down at 10AM. If you’ve got any type of ‘real-world’ RPOs, you’re likely going to need log-file backups every 15 minutes (or less) in order to ensure copies of operations are successfully duplicated. That, in turn, means that you’ll be looking at needing to apply roughly 7 hours worth of log file backups or ~28 log file backups per database in an emergency scenario.)

So, in terms of this second point, I like to point out that manually restoring databases, one file at a time from the GUI is simply an option that does not scale. So, unless you’ve gone ahead and imported backup file meta-data into your luke-warm standby server (using the LOADHISTORY argument of RESTORE VERIFYONLY) and can accurately 'COUNT on that data being up-to-date, you’re going to be wrestling with lots of files in most luke-warm disaster recovery scenarios. As such, the use of a mass-recovery script can be used to very easily automate the process of bringing numerous databases (or even a single database) back online in a disaster scenario.

Better yet, once you’ve got a mass recovery script up and running, you can drop in on your luke-warm failover servers periodically (say, once a week – depending upon the sensitivity and importance of your data) and run this script to test it against Recovery Time Objectives and to likewise test it against RPOs as well.

Creating a Mass Recovery Script

When it comes to building a mass-recovery script, there are sadly a large number of factors and concerns to pay attention to – to the point where there really isn’t going to be a ‘one size fits all’ approach to executing luke-warm failovers that will work everywhere and for every situation. For example, I keep referring to this encapsulation of logic needed to restore databases as a 'mass' recovery script – meaning that, in my experience, it’s pretty typically to need to bring multiple databases online – instead of just a single database. But, in some environments, there might only be a single database. In other environments there might be multiple, different, databases, while in other environments there might be multiple, similar, or even related databases that either have different business priorities or which might infer some sort of hierarchical set of dependencies. In other words, there’s simple a large amount of complexity that has to be adequately addressed when it comes to restoring off-box or luke-warm backups as part of a disaster recovery contingency plan.

To that end, I’ve created two lists – of obvious and not-so-obvious things you’ll want to account for when creating recovery scripts for luke-warm backup servers.

Obvious Concerns For Luke-Warm Backup Scripts

Not-So-Obvious Concerns for Luke-Warm Backup Scripts

Example Mass Recovery Script

As a more ‘hands-on’ approach to outlining some of the specifics that go into creating and using a mass-recovery script, I’m including a very ROUGH copy of a script I put together during the holidays. It’s similar to some scripts I’ve written for clients – but focuses on the use of ‘native’ SQL Server backups (instead of being designed to work against large numbers of databases that are backed up with third-party backup solutions).

/*----------------------------------------------------------------------------------------INSTRUCTIONSA) Don't run this in production. Run it on a luke-warm backup server.B) Check out the Notes/Warnings/Section Below. This script WORKS, but it's very limited and should NEVER be used without testingand validation prior to use with production/emergency data. C) PRESS CTRL+T to kick output into 'text' mode. It runs fine in grid mode (CTRL+D), butis optimized for execution in text-mode.D) Set necessary parameters down in the PARAMETERS section. Make sure that you SQL Server Service account has necessary perms against the foldersspecified. E) To test it out, try running it in @debug = 1, @executeCommandsFirst to see if thesyntax/pathing/etc all look correct. F) Once you've validated that this basic script works and can perform a 'mass' recovery operationagainst your data, revisit the NOTES/WARNINGS/CONSIDERATIONS section and FINISH this scriptfor your environment. In other words, this script is a 'skeleton' designed to give you an ideaof KEY operations - not as a complete DR / Failover solution. ----------------------------------------------------------------------------------------NOTES/WARNINGS/CONSIDERATIONS:- DO NOT RUN THIS ON YOUR PRODUCTION SERVER. IT WILL OVERWRITE YOUR DATABASES. - This Script REQUIRES xp_cmdshell. If you don't know how to turn that on, read up on the SECURITY implications of turning that on in your environment - they're HUGE.- This script ASSUMES that FULL, DIFF, and LOG backups contain FULL, DIFF, and LOG in therespective file-names (somewhere).  - This script works - but should NEVER be used in your environment against productiondata that you care about. - This script is ONLY designed as a rough outline of KEY operations to tackle duringa mass-recovery operation on a LUKE-WARM standby server (or a server where SQL SErver is already installedbut where no DBs are running). - This Script WILL replace DBs if they already exist on the target. If they don't exist on thetarget host, it will re-map their files to the directories specified. - HOWEVER, this script ONLY accounts for a primary .mdf and a single .ldf - if you need more thanthis, you'll need to address that yourself. - This script does NOT account for users/apps being logged into dbs that are being restored. - This script does NOT account for data at the tail-end of your log on a PRODUCTION server. It's designedfor a smoke and rubble/failover/remote-available contingency. - This script does NOT account for permissions/logins. If you need help with thosesee the following video: http://www.sqlservervideos.com/video/copying-and-moving-sql-server-logins/- Error Handling in this script is ONLY stubbed in and has NOT been tested.  ----------------------------------------------------------------------------------------PARAMETERS:@debug- specifies whether or not the commands to be executed will be 'echoed' outto the 'messages' tab during execution of this routine. If @debug is set to 1 andthe @executeCommmands is set to 0, you'll get a verbose output/summary of all commandsthat WOULD have been executed had this script been run normally - but NONE of the actual commands will be run or executed. @executeCommmands - specifies whether or not the actual RESTORE and other commands shouldactually be run or executed. @dbsToRestore - A comma-delimited list of databases to be restored. Datbases are restored inthe order specified in this list. So, databases that should be restored sooner thanothers should be listed first/prioritized as needed. @backupsRoot - This is the path to the base backup folder for the server in question. This script EXPECTS that the base/root folder for backups will have a sub-folderin it for every database you wish/need to restore - and that each subfolder willcontain FULL, [DIFFERENTIAL], and LOG file backups as needed. @dbsRoot - This is the root path to the location where end-user databases are stored. @recoverDatabases - Specifies whether or not databases should be recovered. In most disasterrecovery scenarios, this should be true/1 - unless there's a need to add more transactionlog backups for some reason. However, if trying to set up log shipping or something else, then set this to 0 - so that other log files can be added/etc. */---------------------------------------------------------------------------------------------------------------------------------------------------------------------- PARAMETERS:-- Set the following values BEFORE commencing execution. -- And don't forget to press CTRL+T to kick query/execution output into text mode.----------------------------------------------------------------------------------DECLARE @debug bit = 0 -- echo/output commands to execute:DECLARE @executeCommmands bit = 1 -- execute actual SQLDECLARE @recoverDatabases bit = 1 -- recovery dbs... or leave them capable of adding more log files/etc.-- Define which databases (in order of precedence/priority) to restore:DECLARE @dbsToRestore nvarchar(2000) = N'aapiDenver,aapiCHLA,aapiDev'DECLARE @backupsRoot sysname = N'D:SQLBackups'DECLARE @dbsRoot sysname = N'D:SQLData'SET NOCOUNT ON------------------------------------------------------------------------------------ END PARAMETERS ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Begin Script Logic/FunctionalitySET @dbsToRestore = REPLACE(@dbsToRestore,' ','')DECLARE @targetDatabase sysnameDECLARE @Errors TABLE (dbname varchar(30), phase varchar(20),ErrorCommand nvarchar(max))-- Start Processing: begin by turning @dbsToRestore into a table:DECLARE @dbs TABLE (id int IDENTITY(1,1) NOT NULL, dbname sysname NULL)DECLARE @index int = 1DECLARE @chunk nvarchar(30)IF LEN(@dbsToRestore) < 1 OR @dbsToRestore IS NULLRAISERROR('No Databases Defined For Restoration',18,1) WITH NOWAIT -- FatalWHILE @index != 0 BEGINSET @index = CHARINDEX(',',@dbsToRestore)IF @index != 0SET @chunk = LEFT(@dbsToRestore, @index - 1)ELSESET @chunk = @dbsToRestoreIF(LEN(@chunk) > 0)INSERT INTO @dbs (dbname) VALUES (@chunk)SET @dbsToRestore = RIGHT(@dbsToRestore, LEN(@dbsToRestore) - @index)IF LEN(@dbsToRestore) = 0 BREAKEND-- Initialize by restoring Full backups:DECLARE targets CURSOR FAST_FORWARD FORSELECT dbname FROM @dbsOPEN targetsFETCH NEXT FROM targets INTO @targetDatabaseWHILE @@FETCH_STATUS = 0 BEGINDECLARE @input TABLE (   [output] varchar(500) )DECLARE @backupFiles TABLE ([id] int IDENTITY(1,1),[output] varchar(500))DECLARE @cmd nvarchar(200) = N'dir "' + @backupsRoot + @targetDatabase + '" /B /A-D /OD'IF @debug = 1 PRINT '-- ' + @cmdDELETE FROM @inputINSERT INTO @inputEXEC master..xp_cmdshell @cmd-- now that we've got raw input, we need to push it into the files tableDELETE FROM @backupFilesINSERT INTO @backupFilesSELECT [output] FROM @input DELETE FROM @backupFiles WHERE id < (SELECT MAX(id) FROM @backupFiles WHERE [output] LIKE '%FULL%') OR [output] IS NULLIF @debug = 1SELECT * FROM @backupFilesDECLARE @fullRestore nvarchar(800)DECLARE @fullBackup sysname SELECT @fullBackup = [output] FROM @backupFiles WHERE [output] LIKE '%FULL%'IF EXISTS(SELECT NULL FROM master.sys.databases WHERE name = @targetDatabase) BEGINSET @fullRestore = N'RESTORE DATABASE ' + QUOTENAME(@targetDatabase, '[]') + ' FROM DISK = N''' + @backupsRoot + @targetDatabase + '' + @fullBackup + ''' WITH REPLACE, NORECOVERY'  ENDELSE BEGIN-- Map file locations:DECLARE @FileList TABLE (LogicalName nvarchar(128),PhysicalName nvarchar(128),[Type] char(1),FileGroupName nvarchar(128),Size numeric(20,0),MaxSize numeric(20,0),FileId bigint, CreateLSN numeric(25,0),DropLSN numeric(25,0),UniqueId uniqueidentifier NULL,ReadOnlyLSN numeric(25,0),ReadWriteLSN numeric(25,0),BackupSizeInBytes bigint,SourceBlockSize int,FileGroupId int,LogGroupGUID uniqueidentifier NULL, DifferentialBaseLSN numeric(25,0),DifferentialBaseGUID uniqueidentifier NULL,IsReadOnly bit,IsPresent bit,TDEThumbprint varbinary(32))DECLARE @fileListCommand nvarchar(800)SET @fileListCommand = N'RESTORE FILELISTONLY FROM DISK = N''' + @backupsRoot + @targetDatabase + '' + @fullBackup + ''' 'IF @debug = 1 PRINT '-- ' + @fileListCommandINSERT INTO @FileListEXEC sp_executesql @fileListCommandDECLARE @datafile sysname, @logfile sysnameSELECT @datafile = LogicalName FROM @FileList WHERE FileId = 1SELECT @logfile = LogicalName FROM @FileList WHERE FileId = 2SET @fullRestore = N'RESTORE DATABASE ' + QUOTENAME(@targetDatabase, '[]') + ' FROM DISK =N''' + @backupsRoot + @targetDatabase + '' + @fullBackup + ''' WITH MOVE ''' + @datafile + ''' TO ''' + @dbsRoot + @targetDatabase + '_data.mdf'', MOVE ''' + @logfile + ''' TO ''' + @dbsRoot + @targetDatabase + '_log.ldf'',NORECOVERY 'ENDIF @debug = 1PRINT @fullRestoreIF @executeCommmands = 1 BEGINEXEC sp_executesql @fullRestore-- handle errors:IF @@ERROR <> 0 BEGIN RAISERROR('Error Encountered.',5,0) WITH NOWAITINSERT INTO @Errors SELECT @targetDatabase, 'Restoring FULL Backup', @fullRestoreENDEND-- Look for DIFFerential backups: IF EXISTS (SELECT NULL FROM @backupFiles WHERE [output] LIKE '%DIFF%') BEGIN-- Delete everything < last DIFF backup:DELETE FROM @backupFiles WHERE id < (SELECT MAX(id) FROM @backupFiles WHERE [output] LIKE '%DIFF%')-- restore the diff backup:DECLARE @diffBackup sysnameDECLARE @diffRestore nvarchar(800)SELECT @diffBackup = [output] FROM @backupFiles WHERE [output] LIKE '%DIFF%'SET @diffRestore = N'RESTORE DATABASE ' + QUOTENAME(@targetDatabase, '[]') + ' FROM DISK = N''' + @backupsRoot + @targetDatabase + '' + @diffBackup + '''WITH NORECOVERY 'IF @debug = 1PRINT @diffRestoreIF @executeCommmands = 1 BEGINEXEC sp_executesql @diffRestore-- handle errors:IF @@ERROR <> 0 BEGIN RAISERROR('Error Encountered.',5,0) WITH NOWAITINSERT INTO @Errors SELECT @targetDatabase, 'Restoring DIFF Backup', @diffRestoreENDENDEND-- Start applying Log File Backups:DECLARE logFiles CURSOR FAST_FORWARD FORSELECT [output]FROM @backupFilesWHERE [output] NOT LIKE '%FULL%' AND [output] NOT LIKE '%DIFF%' -- don't grab the FULL backup or the DIFF backup if there was one.ORDER BY id ASCDECLARE @logFilebackupName sysnameDECLARE @command nvarchar(800)OPEN logFilesFETCH NEXT FROM logFiles INTO @logFilebackupNameWHILE @@FETCH_STATUS = 0 BEGINSET @command = N'RESTORE LOG ' + QUOTENAME(@targetDatabase, '[]') + ' FROM DISK = N''' + @backupsRoot + @targetDatabase + '' + @logFilebackupName + ''' WITH NORECOVERY 'IF @debug = 1 PRINT @commandIF @executeCommmands = 1 BEGIN-- don't continue adding logs if we've bumped into errors:IF NOT EXISTS(SELECT NULL FROM @Errors WHERE dbname = @targetDatabase) BEGINEXEC sp_executesql @command-- handle errors:IF @@ERROR <> 0 BEGIN RAISERROR('Error Encountered.',5,0) WITH NOWAITINSERT INTO @Errors SELECT @targetDatabase, 'Applying LOG Backup', @commandENDENDENDFETCH NEXT FROM logFiles INTO @logFilebackupNameENDCLOSE logFilesDEALLOCATE logFiles-- when we're done, recover if directed:IF @recoverDatabases = 1 BEGIN DECLARE @recovery sysname = N'RESTORE DATABASE ' + QUOTENAME(@targetDatabase,'[]') + ' WITH RECOVERY'IF @debug = 1PRINT @recoveryIF @executeCommmands = 1 BEGIN-- don't recover the database if we've bumped into errors:IF NOT EXISTS(SELECT NULL FROM @Errors WHERE dbname = @targetDatabase)EXECUTE (@recovery)ELSE RAISERROR('NOT recovering %s due to errors.',1,0, @targetDatabase) WITH NOWAITENDEND-- load up another db, and go around the horn again... FETCH NEXT FROM targets INTO @targetDatabaseENDCLOSE targetsDEALLOCATE targets-- Report on Errors:IF @executeCommmands = 1 BEGINIF EXISTS(SELECT NULL FROM @Errors) BEGINRAISERROR('Outputting Error Information',1,0) WITH NOWAITSELECT * FROM @ErrorsENDENDGO

The script itself is fairly basic and simple. By specifying base paths to folders where backups for multiple databases can be stored (assuming, of course, that database backups are all stored in their own folders), the script takes advantage of xp_cmdshell (an extended procedure that hackers can wreak the ultimate havoc with on your box if they’re able to take control via SQL Injection) to query the OS and get a list of all backup files in a given directory. It then finds the latest FULL backup, restores it, looks for the latest/last DIFFERENTIAL  backup if there is one (and applies it if needed), and then finds all Transaction Log backups since the last FULL/DIFFERENTIAL backup and applies them one after the other until they’re all gone – at which point it then attempts to RECOVER the database.

All in all, the script is VERY rudimentary, and just ‘automates’ the process of restoring files/media as needed. It also comes with a huge number of caveats (in the NOTES/WARNINGS/CONSIDERATIONS section) – as the script is NOT meant to go into your production environment as a full-blown solution. Instead, it’s best thought of as a bit of scaffolding or an outline of key concepts and concerns that you’ll want to address. But, by taking a script like this and making it your own, you CAN come up with some great solutions that will allow for excellent recovery options to luke-warm standby servers. I’ve actually got solutions similar to this one working for a couple of clients where the use of these kinds of ‘mass recovery’ scripts allow me (or my clients) to periodically go in and verify that RPOs and RTOs can be met in the WORST kind of disaster – such as when existing HA functionality fails, or when an entire data-center is lost. So, in that regard I have full confidence in the overall approach being outline here. But in order for something like this to work for you, I can’t stress enough that you’ll need to take what I’ve provided here as a STARTING POINT from which you’ll need to throw in your own, additional, effort and testing to get it to be a viable solution for your needs.