As a database administrator (DBA), one of the primary responsibilities is to perform backups and restores. However, these tasks can be tedious and time-consuming, especially when dealing with complex scenarios such as restoring from one server to another or using third-party backup/restore utilities.

To alleviate some of the pain associated with backups and restores, I have developed two procedures: sp_ABBackupDb and sp_ABRestoreDb. These procedures aim to simplify the backup and restore process and make it more efficient.

One of the key features of these procedures is their ability to intelligently handle file specifications. For example, you can specify the database name, current datetime, or use wildcards to match the most recent file. Additionally, the procedures can automatically prepend the backup path if a relative filespec is provided.

Another useful feature is the dynamic construction of the MOVE clause on restores. The procedures will intelligently match logical filenames on the existing database with the files to be restored, ensuring a seamless restore process.

Furthermore, the procedures can handle the killing of existing connections before a restore and the fixing of user logins after a restore using sp_change_user_login. This eliminates the need for manual intervention and ensures that the restored database is ready for use.

Additionally, the procedures provide options to remove old backups after a specified time interval, specify additional WITH clause options for backup or restore, and even verify backups after completion.

To use these procedures, simply call them from any database (except the one you wish to restore) using the EXEC command. The procedures will return a zero if no errors occur, making it easy to incorporate them into your scheduled backup and restore tasks.

By using sp_ABBackupDb and sp_ABRestoreDb, you can benefit from simplicity and consistency in your backup and restore operations. You can use a single backup command for all your databases, even across multiple servers with different backup paths. Adhoc backups become simple, and the restore process is streamlined, eliminating the need for manual intervention and reducing the chances of errors.

These procedures are compatible with popular backup utilities such as SQL Litespeed and Redgate’s SQL Backup, ensuring consistent handling of backups regardless of the software used.

If you encounter any issues or have suggestions for enhancements, please provide your feedback. Your input is valuable in improving these procedures and making them even more useful for the SQL Server community.

Download the zip file containing the procedures here.