How to check in recovery database SQL Server?

by
How to check in recovery database SQL Server

Checking the recovery database is an important task for any SQL Server administrator. The recovery database holds backup information that allows you to restore or recover your database in the event of damage or loss. Knowing how to check this database ensures you can verify it is in good shape and take action if needed.

Why Check the Recovery Database?

There are a few key reasons you’ll want to periodically check in on your SQL Server recovery database:

  • Verify backups are completing successfully – The recovery database tracks all your backup jobs and whether they complete successfully or fail. Checking it allows you to catch any failed backups.
  • Check for missing backups – You can also scan the recovery database to ensure all scheduled backups are present and accounted for.
  • View backup history – The recovery database maintains a complete history of all backup jobs. You can use this to find out when a database or log was last backed up.
  • Confirm restore ability – By verifying healthy recovery database contents, you can confirm your ability to restore or recover databases if needed.
  • Troubleshoot issues – The recovery database can help troubleshoot backup or restore problems by pinpointing where in the backup chain an issue first occurred.

Regularly checking in on the recovery database gives you assurance your backups are working and available when needed.

Accessing the Recovery Database

The recovery database in SQL Server is actually a set of system tables stored in the master database. To access it and check its contents, you’ll query these system tables directly. Here are the main tables that comprise the recovery database:

  • backupfile – Contains details on data and log backup files.
  • backupfilegroup – Maps backup files to their corresponding filegroups.
  • backupmediafamily – Groups related media sets from backup operations.
  • backupmediaset – Details on backup media sets.
  • backupset – Information on each backup set.
  • restorefile – Tracks files being restored.
  • restorefilegroup – Maps restore files to filegroups.
  • restorehistory – Restore operations history and results.

These system tables contain all the historical data you need to evaluate your SQL Server backups. To query them, you’ll use standard SELECT statements while specifying the master database. For example:

USE master;
SELECT * FROM backupmediafamily;

This would let you access the backupmediafamily data to check info on media sets. Other tables like backupset could then be queried to get backup details.

Key Recovery Database Queries

Here are some useful queries to help check the recovery database:

See All Backup History

USE master;

SELECT
   b.backup_start_date,
   d.name AS database_name,  
   b.user_name,
   bm.physical_device_name,
   b.backup_finish_date
FROM 
   msdb.dbo.backupset b
INNER JOIN
   msdb.dbo.backupmediafamily bm ON b.media_set_id = bm.media_set_id 
INNER JOIN
   sys.databases d ON b.database_name = d.name
ORDER BY
   b.backup_start_date DESC;

This query joins the backupset, backupmediafamily, and sys.databases tables to retrieve a complete list of all backups performed on all databases. It pulls out useful details like the database name, physical device backed up to, username performing the backup, and start/finish times.

Verify Recent Full Backup

USE master 

SELECT 
   database_name AS 'Database',
   user_name AS 'User',
   backup_start_date AS 'Backup Date'
FROM msdb.dbo.backupset 
WHERE type = 'D'
  AND backup_start_date > DATEADD(dd, -7, GETDATE());

Retrieving only full database backups taken in the past week, this quickly verifies your recent full backup history.

Get Latest Backup Info

  
USE master
  
SELECT  
  bs.database_name AS 'Database', 
  bm.physical_device_name AS 'Physical Backup Device',
  bs.backup_finish_date AS 'Latest Backup Date'
FROM msdb.dbo.backupmediafamily AS bm  
JOIN msdb.dbo.backupset AS bs ON bm.media_set_id = bs.media_set_id 
WHERE bs.database_name = 'MyDatabaseName'
ORDER BY bs.backup_finish_date DESC;

This gets you the latest backup date and device for a specific database.

Summarize Backup Status

USE msdb

SELECT
   d.name AS DatabaseName,
   MAX(b.backup_finish_date) AS LastFullBackup,
   MAX(b.backup_finish_date) AS LastDiffBackup, 
   MAX(l.backup_finish_date) AS LastLogBackup
FROM sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset b 
   ON b.database_name = d.name 
   AND b.type = 'D'
LEFT OUTER JOIN msdb.dbo.backupset l
   ON d.name = l.database_name 
   AND l.type = 'L'
GROUP BY 
   d.name

By joining backupset with sys.databases, this summary query tells you the last full, differential, and transaction log backups for all databases.

Checking Backup History for Corruption

A key task is verifying your backup history doesn’t contain any corrupted or incomplete backups. Here are some methods to check for corruption:

Verify successful backup state

USE msdb
SELECT 
   database_name,
   backup_finish_date,
   backup_finish_date - backup_start_date AS backup_time,
   CASE backup_finish_date WHEN NULL THEN 'Failed' ELSE 'Success' END AS backup_result
FROM backupset
WHERE database_name = 'MyDatabase'
ORDER BY backup_finish_date DESC;

This checks the backup finish times and compares to start times to ensure backups completed successfully.

Check media set status

USE master
SELECT 
   media_set_id, 
   media_family_count,
   CASE media_set_status WHEN 0 THEN 'Active' WHEN 1 THEN 'Disabled' END AS media_status
FROM sys.backupmediafamily;

Here we check for disabled or unusable media sets that could indicate corrupted backup files.

Review backupset table

USE msdb
SELECT * 
FROM backupset 
WHERE database_name = 'MyDatabase'
  AND type = 'D'
ORDER BY backup_set_id DESC;

Manually examine the latest contents of backupset for your database to check for any anomalies in backup dates, sizes, durations, etc.

Validate CHECKSUM option

RESTORE VERIFYONLY
FROM DISK = 'Z:\SQLServerBackups\MyDatabase_full.bak'
WITH CHECKSUM;
GO

RESTORE VERIFYONLY checks database backup integrity by validating the CHECKSUM values match. This detects backup corruption issues.

Monitoring Backup and Restore Operations

In addition to historical reporting, the recovery database also provides monitoring of currently executing backup and restore jobs. Useful queries include:

--Currently running backups
SELECT * FROM msdb.dbo.backupset WHERE is_damaged = 1

--Currently running restores
SELECT * FROM msdb.dbo.restorehistory WHERE restore_finish_date IS NULL

--Last 10 backup or restore errors
SELECT TOP 10 * FROM msdb.dbo.backupset WHERE is_damaged = 1
SELECT TOP 10 * FROM msdb.dbo.restorehistory WHERE restore_finish_date IS NULL

These let you see active and recent failed operations that might need attention.

Creating Alerts and Notifications

Once you’ve crafted recovery database queries to check backup status, history, and active jobs, add them to SQL Server Agent jobs to run on schedules. Then configure notifications via email, pager, or net send to alert you in case of issues like:

  • Failed backup jobs
  • Missed backups
  • Disabled media sets
  • Corrupted backups
  • Restore failures

Automated monitoring and alerting helps proactively notify you of any problems with backups or restores.

Best Practices for Checking the Recovery Database

Some best practices to follow when monitoring your SQL Server recovery database:

  • Schedule daily/weekly jobs to query backup history and status.
  • Verify presence of all scheduled full, differential, and log backups.
  • Check for successful and completed backup jobs.
  • Monitor active backup and restore jobs for failures.
  • Enable backup checksum option for corruption checks.
  • Query recovery tables directly using USE master in scripts.
  • Automate alerts for failed backups, missing backups, or other issues.

Closely watching over your backup info in the recovery database helps avoid failed restores and ensure availability.

Conclusion

The SQL Server recovery database is a crucial resource for monitoring backups and restores. By directly querying its system tables using SELECT statements, you can gain insight into your backup history, success rates, latest backups, active jobs, and more. Build scripts to automate these recovery database checks daily or weekly. Enabling notifications on jobs gives you alerts on failed backups or other issues.

Following best practices for frequently checking in on the recovery database protects you from backup problems before they cause business disruption. Alerts act as an early warning system for backup failures or corruption. With robust recovery monitoring in place, you gain confidence in quickly restoring databases from reliable backups.