To make sure you have the information necessary to successfully (and easily) restore from the file and filegroup backup strategy I discuss in the main article, you need to ensure the accessibility of msdb by backing it up often. However, by default, msdb's recovery model is set to Simple. This recovery model prevents quick, easy, and frequent log backups; the Simple recovery model is typically used for smaller databases or development databases where data loss isn't a problem. However, having access to msdb during recovery can simplify recovery. Regular backups of msdb and frequent log backups will ensure that the information within msdb is protected. To do this, you must change the recovery model. Unfortunately, even if you change the recovery model to Full, the SQL Server Agent resets the status back each time the SQL Server Agent starts up. To solve this problem, you can use the following series of steps:
- Create a job that resets msdb's recovery model on agent startup, then does a full database backup of msdb.
- Create a job that performs log backups every n minutes. I suggest something fairly frequent—for example, every 10 minutes.
- Copy the msdb backups (both the database and log backups) to an offsite location to ensure accessibility in case of site failure. The usage of msdb on another server will be limited because some tables within msdb rely on the servername. If msdb is being restored to another server solely for recovering backup history from sysbackuphistory, restore msdb to a database with a different name. If you need to restore and use msdb on another server, I recommend that you script out the jobs and rescript them in. However, the following query will help you update the msdb database to use the new server's name and will update all jobs on the new server:
UPDATE msdb.dbo.sysjobs
SET originating_server = 'NewServerName'
WHERE originating_server = 'OldServerName'
