Backup and Restore - More Intelligent Than Before

Backups and restores are some of the primary responsibilities of a DBA, but they're not very fun. Especially when you've got to concoct the correct MOVE statement when restoring from one server to another, or find the latest YYYYMMDDHHMM filename to use for a restore, or get the quoting just right if calling a third-party backup/restore utility such as Imceda's SQL Litespeed or Redgate's SQL Backup. And how many times have you started a restore but forgot to kill existing connections? Or, after the restore from another server, forgot to run sp_change_user_login to match logins with newly restored user ids?

To alleviate some of the pain, I wrote the procedures sp_ABBackupDb and sp_ABRestoreDb. My goal was to make backup/restore as easy as it could be for me. Download the zip file here.

Naming convention note: sp_ is the magical prefix that looks in the master database for a stored procedure, and uses the current database's system tables when doing so. The AB portion follows a Microsoft convention of using sp_MS* names for additional stored procedures they added but didn't document as well. AB is the pre-merger initials of the company I work for.

Features

• Determines whether SQL Litespeed or SQL Backup is present, and by default uses them if they are

• Can backup/restore a list of databases, including {UserDbs}, {SystemDbs}, or {AllDbs}

• Intelligent file specifications than can include:

  • Database name

  • Current datetime (backup only)

  • Wildcards that match the most recent file

  • Prepend backup path if relative filespec provided

  • Sensible defaults when no filespec provided

  • Multiple filespecs can be specified for parallel backup/restore

• Dynamic construction of the MOVE clause on restores:

  • If database to restore exists, use existing database physical filenames

  • If database to restore doesn't exist, put data on the Default Data and Default Log paths specified in Enterprise Manager and saved in the registry

  • If logical filenames on existing database and file to restore differ, match them intelligently

  • Give up if the number of logical files by type (data,log) in the backup differs from the database

• Kill existing database users before restore

• Fixup user logins after restore with sp_change_user_login

• Remove old backups after any number of hours, days, or weeks

• Can specify additional WITH clause options to pass through to backup or restore

• Optionally verify backups

To use the procedures, call them from any database (except, of course, a database that you wish to restore) using EXEC. The return code of the procedure is zero if no errors occur, otherwise the highest-numbered error encountered is returned (but see the Limitations section for a caveat).

sp_ABBackupDb Parameters

sp_ABRestoreDb Parameters

Backup Examples

-- Backup Northwind to E:\MSSQL\MSSQL$Inst\BACKUP\Northwind_db_HHHHMMDDHHMM.xxx
-- If SQL Backup present use it, xxx extension will be .SQB
-- If Litespeed present use it otherwise native, xxx extension will be .BAK
EXEC sp_ABBackupDb 'Northwind', 'E:\MSSQL\MSSQL$Inst\BACKUP\&_db_@'


-- Backup Northwind to [BackupDir]\Northwind\Northwind_db_HHHHMMDDHHMM.xxx
USE Northwind
EXEC sp_ABBackupDb


-- Differential Backup of Northwind and pubs.  Replace the most recent file
-- in the pattern [BackupDir]\Northwind\Northwind_diff_* and [BackupDir]\pubs\pubs_diff_*
-- Use native SQL backup.  Verify backup file after each backup completes.
EXEC sp_ABBackupDb 'Northwind,pubs', '&\&_diff_*', @Options = 'DIFFERENTIAL', @Type = 'SQL', @Verify=1


-- Backup all databases except master, model, msdb, and tempdb to backup directory.  Do not put 
--  files in subdirectories.  Filename defaults to [Database]_db_YYYYMMDDHHMM.xxx, where xxx is 
--  either SQB or BAK.  Delete backups that are 3 days or more old.
EXEC sp_ABBackupDb '{UserDbs}', @DeleteAfter = '3 days', @CreateDir = 0


-- Backup master, model, msdb, and also pubs, with filenames as above.
EXEC sp_ABBackupDb '{SystemDbs},pubs'


-- Backup Northwind and pubs using three backup files.
EXEC sp_ABBackupDb @Databases = 'Northwind,pubs', @FileSpec = '&_part1,&_part2,&_part3'

Restore Examples

-- Restore Northwind from most recent backup found in E:\MSSQL\MSSQL$Inst\BACKUP
--  with a filename starting with Northwind_db_.
-- If SQL Backup or Litespeed is present, assume it was used to make the backup.
EXEC sp_ABRestoreDb 'Northwind', 'E:\MSSQL\MSSQL$Inst\BACKUP\&_db_*'


-- Restore Northwind from most recent file matching [BackupDir]\Northwind\Northwind*
--  or [BackupDir]\Northwind* if Northwind dir not present.
EXEC sp_ABRestoreDb 'Northwind'


-- Restore Northwind and pubs.  Use the most recent file.
-- in the pattern [BackupDir]\Northwind\Northwind_diff_* and [BackupDir]\pubs\pubs_diff_*
-- Use native SQL restore.  Use the NORECOVERY option to permit additional log restores.
EXEC sp_ABRestoreDb 'Northwind,pubs', '&\&_diff_*', 'NORECOVERY', 'SQL'


-- Restore all databases (master is intentionally excluded from {AllDbs} and {SystemDbs}).
-- Look for most recent file like [BackupDir]\[Database]\[Database]* if [BackupDir]\[Database]
-- exists for ALL databases, otherwise look for files like [BackupDir]\[Database]*.
USE master
EXEC sp_ABRestoreDb '{AllDbs}'


-- Restore Northwind and pubs using three backup files each.
EXEC sp_ABBackupDb 'Northwind,pubs', '&_part1,&_part2,&_part3'


-- Create a new database NewPubs from the most recent backup of pubs on the share \\Prod\share\.
-- Create the new database devices in the default data and log device locations.
EXEC sp_ABRestoreDb 'NewPubs', '\\Prod\share\pubs_db_*'

Benefits

Using sp_ABBackupDb provides the benefits of simplicity and consistency to your scheduled backups. You can get away with a single backup command for all of your databases. You can use the same backup command across multiple servers, even when paths to the backups differ, by allowing the default backup directory to be prepended. You can replace backups that are part of a database maintenance plan by using the @DeleteAfter option. When you have a mix of third-party and native backup software in your environment, you can handle them consistently. Adhoc backups become simple.

The major benefits, though, are seen on the restore side. Coding your cross-server restore directly requires fiddling with the MOVE clause, unless the paths on the two servers are identical. Then you need to lookup just what those logical file names are, and get the pathing correct to the current database physical filenames. Of course, for adhoc restores you can use the restore wizards within SQL Enterprise Manager, SQL Litespeed MMC console, or the SQL Backup GUI. Maybe it's just me, but these seem to require lots of clicks, and tend to confuse my little brain. And of course, if you forgot to kill the existing connections first, you get to start over at square one with the GUIs. Once it starts, you get to watch the status bar grow. Then, what are the chances you remember to run sp_change_users_login? How fun is it to do so for every stinking user account? Oh, and hope you only needed a single restore, or you get to play again.

sp_ABRestoreDb solves all these niggling issues. It's great for restoring the latest production database backups to a disaster recovery, QA, or development machine. You can literally get the restores down to a single stored procedure call, supplying the list of databases and an optional file specification.

SQL Backup Gotchas Avoided

Redgate's SQL Backup is great product for the price, and we use it on most of our servers. To be sure, though, it has its quirks. Most insidious is its instance handling. Assume you have two instances of SQL Server on a single machine, one on the default instance and one on a named instance, and the SQL Backup extended stored procedure is installed on both. If you connect to the named instance and call the extended procedure master.dbo.sqlbackup, you backup or restore a database on that instance, right? Wrong! By default, the interface assumes the default instance, no matter which instance it is called from. Yikes! sp_ABBackupDb and sp_ABRestoreDb always pass the name of the current instance to master.dbo.sqlbackup to avoid this gotcha.

Another fun exercise is SQL Backup error detection. If there's one place I don't want a silent failure, it's on my backups and restores. But I've found SQL Backup to be inconsistent at returning an error code, even at the latest version (v3.2.05) that was supposed to help with this. To detect SQL Backup errors, the extended procedure output is sent to a table and then parsed. The highest numbered error code will be saved and used as the return code. A return code of 0 from sp_ABBackupDb or sp_ABRestoreDb means no errors detected.

Speaking of parsing, one little issue spun me in circles for hours. The output from SQL Backup is a C-style string terminated with a zero-byte. But SQL Server doesn't see the zero-byte as a termination. There could be, and is, lots of junk after that byte that SQL happily considers part of the string. However most output tools, including Query Analyzer, don't display past a zero byte! Unless you issue a len() or datalength() command (as I did in my third debugging hour) you can't tell that SQL has more string than it shows. The solution is to use LEFT(col1, charindex(char(0), col1) - 1) when reading from the output table.

Helper Function and Procedures

The procedures sp_ABBackup and sp_ABRestore make use of a custom system function named ::fn_Split. By creating this as a system function, it will be found in the master database regardless of the current database (much like an sp_* procedure). This function splits a string on a delimiter, and returns the result as a two column table. Column Item contains an instance of the data, and column ItemId holds an identity column identifying the sequence of this instance. Parsing comma-separated input is easy with this function. It gets used to piece apart the database string, the file specification string (when multiple filespecs are specified), and to separate Litespeed's stored procedure-based options from the typical WITH-based options.

Before restoring, procedure sp_ABKillDbUsers is called to clear existing database connections. This could be done inline to the procedure, but separating it out makes it available for other uses. After the restore, we need to run sp_change_users_login procedure across all SQL (non-Windows) logins. sp_ABFixUsersLogin does the job nicely.

Coding Techniques

Using ::fn_Split works well to parse apart comma-separated values, but how do we get them back together again? A WHILE loop can be used, but that's a lot of error prone coding. At several points in the code, you'll find statements like this:

SELECT  @String = NULL
SELECT  @String = ISNULL(@String + ', ', '') + Item
  FROM  ::fn_Split(@ParmString, ',')
  ORDER BY ItemId

The second SELECT statement does the work by appending a comma and adding the current row for every row after the first. On the first row, @String IS NULL and is changed to an empty string, so that just the first row's data is added.

Another technique is to be wary of SQL injection. Since we're executing using the EXEC() statement, we need to be cautious of hackers inputting unwanted SQL Syntax within the parameters. A quick check at the start of the procedure helps prevent this, though I'm not at all certain its foolproof. Execute rights for these procedures should be limited to administrators.

Limitations

Presently sp_ABBackupDb can't determine which additional options should be used for the verify command. I didn't want to add another parameter for what seemed of limited use.

Native SQL Backups will often handle errors by blowing up the batch, so you can't rely on an error code return in that case.

Feedback

If you find the procedures here to be of use, let me know in the "Your Opinion" section. If you find bugs or want to suggest some enhancements, I'll do like my kindergartner and put my listening ears on.