Backing Up msdb: Jobs, History and Mail Live There

Backing up msdb saves the part of your server that no user-database backup contains. Jobs, schedules, backup history and Database Mail settings all live there. Lose it, and the server comes back quiet.

The server that came back quiet

Picture a rebuilt server on a Monday morning. Every user database is restored and the application connects. Everyone is happy.

Then nobody gets the usual 6 AM report email. The nightly index job never ran. The backup jobs are gone, so nothing is being backed up either. The data came back, but the work around the data did not. That work lives in msdb.

See what lives in msdb

Let me show it with a small Agent job. The demo creates a server-level object, a job named DemoMsdbJob, and removes it at the end. The job has one step that runs a harmless SELECT. If your SQL Server Agent service is stopped, you will see a note that Agent cannot be notified. That is fine for this demo.

IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DemoMsdbJob')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DemoMsdbJob';

EXEC msdb.dbo.sp_add_job @job_name = N'DemoMsdbJob',
     @description = N'Demo job for the msdb post';
EXEC msdb.dbo.sp_add_jobstep @job_name = N'DemoMsdbJob',
     @step_name = N'Say hello', @subsystem = N'TSQL', @command = N'SELECT 1;';
EXEC msdb.dbo.sp_add_jobserver @job_name = N'DemoMsdbJob';

Now ask msdb where that job went. It is a plain row in a plain table, and the step sits in another table beside it.

SELECT j.name AS job_name, s.step_name
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
WHERE j.name = N'DemoMsdbJob';

SELECT N'Agent jobs' AS lives_in_msdb, COUNT(*) AS row_count FROM msdb.dbo.sysjobs
UNION ALL
SELECT N'Job schedules', COUNT(*) FROM msdb.dbo.sysschedules
UNION ALL
SELECT N'Mail profiles', COUNT(*) FROM msdb.dbo.sysmail_profile
UNION ALL
SELECT N'Backup history rows', COUNT(*) FROM msdb.dbo.backupset;

The first result shows our job and its one step. The second is a quick inventory of what you would lose. Your counts will differ from mine. The Agent jobs count includes DemoMsdbJob, so it is at least 1. A backup of your sales database contains none of these rows.

Check the last recorded backup of msdb

Now ask the useful question: when was msdb last backed up? The first query reads the backup history. A date of NULL means no full backup of msdb is on record. On my test server it showed a date from the same morning, because that server already had a recent msdb backup. The second query lists the files, and the size is configured capacity, not the size of a backup.

SELECT MAX(backup_finish_date) AS LastRecordedFullBackup
FROM msdb.dbo.backupset
WHERE database_name = N'msdb' AND type = 'D';

SELECT name, type_desc, CONVERT(bigint, size) * 8192 AS ConfiguredBytes
FROM sys.master_files
WHERE database_id = DB_ID(N'msdb')
ORDER BY file_id;

The files on my test server were roughly 19 MB for data and 11 MB for the log. Yours will differ. Both results come from msdb itself, which is a little funny: msdb remembers its own backups.

Generate the backup command, then verify the file

I would rather you see the command before it runs. This block builds it for the server’s default backup folder and prints it. Paste the result into a new window and run it. The service account needs write access to that folder. If your server reports no default folder, the block falls back to C:\Temp\, which you create first.

DECLARE @folder nvarchar(260) =
    ISNULL(CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultBackupPath')), N'C:\Temp\');
IF RIGHT(@folder, 1) <> N'\' SET @folder += N'\';

DECLARE @file nvarchar(400) =
    @folder + N'msdb_' + FORMAT(SYSDATETIME(), 'yyyyMMdd_HHmmss') + N'.bak';

SELECT N'BACKUP DATABASE msdb TO DISK = N''' + @file + N''' WITH CHECKSUM;' AS BackupCommand;

A history row is only a note that a backup happened. It does not prove the file is still there or that it restores. Here is what a missing file looks like when you ask SQL Server to check it.

RESTORE VERIFYONLY FROM DISK = N'C:\Temp\msdb_that_was_never_taken.bak';

Error 3201 says SQL Server cannot open the backup device, which is the file. Error 3013 follows it. Run the same check on your real msdb file after every backup. Better still, restore a copy on a test server now and then and look at the job list. Remember that SQL Server Agent must be stopped while msdb is restored.

Keep scripts as a second safety net

An msdb backup is not a move-anywhere package. It cannot be restored on an older version of SQL Server. So also keep your job definitions as scripts, and store the mail setup steps next to them. Take an extra msdb backup after any big job change. Alert on a failed backup job too, because a silent schedule is very good at looking finished.

When you are done with the demo, remove the job.

IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DemoMsdbJob')
    EXEC msdb.dbo.sp_delete_job @job_name = N'DemoMsdbJob';

Add msdb to the list of databases you check every week.

Recovery is not only your data, it is the work around your data.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.