Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

More Intelligent Backup and Restore

By Vince Iacoboni,

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.


  • 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

Parameter Default Information
@Databases DB_NAME() Comma-separated list of databases to backup. Can include {UserDbs}, {SystemDbs}, or {AllDbs}. {SystemDbs} and {AllDbs} include master on backup.
@FileSpec &\&_db_@
File specification that determines the name of the backup output file(s). & is replaced by the name of the database, and @ is replaced by the current datetime in YYYYMMDDHHMM format. The specification may include the wildcards * and ?, in which case the LATEST file matching the pattern is used. If no drive letter or UNC path is given, prepends the path specified in the BackupDirectory registry key. If no extension is provided, will use .BAK for native SQL and Litespeed backups and .SQB for SQL Backup. If the parameter is not provided, will use the specification &\&_db_@ or &_db_@ if @CreateDir = 0.
@Options   Additional backup options to be appended to the WITH clause. For SQL Litespeed backups, options starting with @ are additional stored procedure parameters rather than added to the @With parameter.
@Type (dynamic) The backup vendor. Valid values are "SQL" for native SQL Server backup, "Litespeed" for Imceda's SQL Litespeed and "SQB" for Redgate's SQL Backup. If not supplied, checks for the existence of extended stored procedures to determine if the third-party backup products should be used.
@Verify 0 Verifies backup file after completion of each backup if set to 1.
@DeleteAfter   Deletes backup files matching the file specification if the YYYYMMDDHHMM component is older than the specified time interval and the backup (and verify if specified) succeeded. Format is a number followed by a space and one of the words "hour", "day", or "week" with an optional plural. The file specification must contain @.
@CreateDir 1 Creates file directories if not already existing. Also, if the @FileSpec parameter is not supplied, places the backup in directories named for the database. If 0, aborts the backup if directory does not exist.
@DryRun 0 If 1, does not perform any backup or file deletions, but displays commands that would be run.

sp_ABRestoreDb Parameters

Parameter   Default    Information
@Databases (required) Comma-separated list of databases to restore. Can include {UserDbs}, {SystemDbs}, or {AllDbs}. {SystemDbs} and {AllDbs} DO NOT include master for restores, since this would stop the current instance of SQL Server. This parameter is required.
@FileSpec &\&*
File specification that determines the name of the backup file(s) to restore. & is replaced by the name of the database. The specification may include the wildcards * and ?, in which case the LATEST file matching the pattern is used. If no drive letter or UNC path is given, prepends the path specified in the BackupDirectory registry key. If the parameter is not provided, will use the specification &\&* if ALL databases to be restored have such directories existing, otherwise the specification &* will be used.
@Options   Additional restore options to be appended to the WITH clause. For SQL Litespeed backups, options starting with @ are additional stored procedure parameters rather than added to the @With parameter. Do not include the MOVE clause, as this is handled automatically.
@Type (dynamic) The backup vendor. Valid values are "SQL" for native SQL Server backup, "Litespeed" for Imceda's SQL Litespeed and "SQB" for Redgate's SQL Backup. If not supplied, checks for the existence of extended stored procedures to determine if the third-party backup/restore products should be used.
@DryRun 0 If 1, does not perform any restores, but displays commands that would be run.

Backup Examples

	-- Backup Northwind to E:\MSSQL\MSSQL$Inst\BACKUP\
	-- 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\ 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], 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_*'


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.


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.


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.

Total article views: 13384 | Views in the last 30 days: 11
Related Articles

SQL Exercises for PUBS and Northwind SQL Sevrer database

Pubs and Northwind database





Backup or Restore progess

This procedure provides you the progess of the database backup or restore. We can execute the proced...


Restore from Backup with Replicated Database

Restore from Backup with Replicated Database


Restore with no backup

Database dropped, no backup. Need to restore


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones