Backup and Restore History

Last updated: October 26, 2023

This document provides information on how to manage and view the backup and restore history in SQL Server. Understanding backup history is crucial for effective disaster recovery and data management.

Overview of Backup and Restore History

SQL Server maintains a history of backup and restore operations, allowing administrators to track when backups were performed, what type of backup it was, and the success or failure status of the operation. This history is stored in the msdb database, specifically in tables like backupset, backupmediafamily, and restorehistory.

Key Tables for History Information

• msdb.dbo.backupset: Contains a row for each backup set. A backup set is a consistent set of files that make up a backup.
• msdb.dbo.backupmediafamily: Contains information about the backup media used for each backup set.
• msdb.dbo.restorehistory: Contains information about restore operations performed on databases.

Querying Backup History

You can retrieve backup history information by querying the system tables in the msdb database. The following Transact-SQL (T-SQL) query shows how to get a list of recent full database backups:

SELECT
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    bs.expiration_date,
    bs.backup_size,
    bmf.physical_device_name,
    bs.user_name,
    bs.server_name,
    CASE bs.type
        WHEN 'D' THEN 'Full Database'
        WHEN 'I' THEN 'Differential Database'
        WHEN 'L' THEN 'Log'
        WHEN 'F' THEN 'File'
        WHEN 'G' THEN 'Differential File'
        WHEN 'P' THEN 'Partial'
        WHEN 'Q' THEN 'Differential Partial'
        WHEN 'X' THEN 'Filegroup'
        WHEN 'Y' THEN 'Differential Filegroup'
    END AS backup_type
FROM
    msdb.dbo.backupset bs
JOIN
    msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE
    bs.type = 'D' -- Filter for Full backups
ORDER BY
    bs.backup_finish_date DESC;
                

Querying Restore History

To view restore history, you can query the msdb.dbo.restorehistory table. This table records details about each restore operation, including the database name, restore date, and the user who performed the restore.

SELECT
    rh.restore_date,
    rh.destination_database_name,
    rh.user_name,
    rh.backup_set_id,
    bs.backup_start_date AS backup_date_for_restore,
    bs.server_name,
    CASE rh.restore_type
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS restore_type
FROM
    msdb.dbo.restorehistory rh
LEFT JOIN
    msdb.dbo.backupset bs ON rh.backup_set_id = bs.backup_set_id
ORDER BY
    rh.restore_date DESC;
                

Common Restore Operations to Track

• Full Database Restores
• Differential Database Restores
• Log Restores

Managing Backup History

SQL Server automatically purges old backup history records from the msdb database based on the backup retention settings configured. You can also manually clean up backup history using the sp_delete_backuphistory stored procedure.

Using sp_delete_backuphistory

This stored procedure allows you to delete backup history records older than a specified date. It's good practice to run this periodically to keep the msdb database size manageable.

-- Delete history older than 30 days
EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = '2023-09-26';
                

Note: Ensure you replace '2023-09-26' with the desired date.

Best Practices

• Regularly review your backup and restore history to ensure backups are succeeding and that your restore strategy is sound.
• Implement a log shipping or Always On Availability Groups solution for high availability and disaster recovery.
• Store backups on separate physical media from your production servers.
• Test your restores regularly to validate backup integrity.
• Use a consistent naming convention for your backup files and databases.

For more detailed information on specific backup and restore scenarios, please refer to the related documentation linked in the navigation pane.