Backup and Restore Stored Procedures

This document provides a comprehensive guide to the system stored procedures used for backup and restore operations in Microsoft SQL Server. These procedures offer granular control over backup and restore processes, allowing for automation and integration into custom solutions.

Introduction to Backup and Restore Stored Procedures

SQL Server provides a set of built-in stored procedures that encapsulate the functionalities for creating backups and restoring databases. Utilizing these procedures is often preferred over direct T-SQL commands for their flexibility, error handling, and ability to script complex scenarios.

Key Stored Procedures

Backup Procedures

  • sp_addumpdevice: Adds a backup device (e.g., disk file, tape drive) to the SQL Server instance.
  • sp_backupdatabase: Creates a database backup. This is a flexible procedure that supports various backup types (full, differential, transaction log) and options.
  • sp_set_backup_filter: Sets a filter for backups, allowing you to exclude specific files or filegroups.

Restore Procedures

  • sp_addumpdevice: Used to add a backup device for restore operations as well.
  • sp_restorefile: Restores a single file or filegroup from a backup set.
  • sp_restoredatabase: Restores a database from a backup set. Supports various restore options, including WITH MOVE for relocating files.
  • sp_restorelog: Restores a transaction log backup.
  • sp_get_backup_info: Retrieves information about backup sets from a backup device.

Example Usage

Creating a Full Database Backup

The following example demonstrates how to add a backup device and then perform a full database backup using stored procedures.

-- Add a disk backup device
USE master;
EXEC sp_addumpdevice 'disk', 'MyDatabaseFullBackup', 'C:\Backups\MyDatabase_Full.bak';
GO

-- Perform a full backup of MyDatabase
BACKUP DATABASE MyDatabase
TO MyDatabaseFullBackup
WITH COMPRESSION, STATS = 10;
GO

Restoring a Database

This example shows how to restore a full database backup. Note the use of WITH MOVE to specify new locations for the database files.

-- Restore the database, moving data and log files
USE master;
RESTORE DATABASE MyDatabase
FROM MyDatabaseFullBackup
WITH
    MOVE 'MyDatabase_Data' TO 'C:\SQLData\MyDatabase.mdf',
    MOVE 'MyDatabase_Log' TO 'C:\SQLLogs\MyDatabase_Log.ldf',
    REPLACE, -- Overwrite if the database already exists
    STATS = 5;
GO

Important Considerations

  • Always specify appropriate backup options to ensure data integrity and recovery capabilities.
  • Use WITH STATS to monitor the progress of large backup and restore operations.
  • Consider using backup compression to reduce the size of backup files and speed up backup and restore operations.
  • Ensure that the SQL Server service account has the necessary permissions to read from and write to the specified backup locations.
Tip: For automated backup and restore tasks, consider creating SQL Server Agent Jobs that execute these stored procedures.
Note: The sp_backupdatabase and sp_restoredatabase procedures are deprecated in favor of the BACKUP DATABASE and RESTORE DATABASE Transact-SQL statements, but are still functional. It is recommended to use the T-SQL statements for new development.

Further Reading