It’s easy to backup the resourcedb with Minion Backup. And while it’s not a setting (yet), it only takes one step.
I’m going to add some batch post code to the BackupSettingsServer table that will perform this action for me.
SET BatchPostCode = ‘xp_cmdshell ” xcopy “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\mssqlsystemresource.*” c:\MyBackups\Resource /Y”’
WHERE DBType = ‘System’;
Here’s a screenshot of the results:
The resourcedb doesn’t backup like regular DBs. It has to be copied to the location you want to back it up to. And as you can see, that’s what the above code does. I’ve added this to the system database BatchPostCode, but you could add it to the precode, or even pre/post code for any of the databases on the system. You don’t need to backup the resourcedb very often so I wouldn’t make it part of every full backup, and especially not part of any log backups. And of course, this is a basic script. You could easily wrap this command into a stored procedure and put any amount of logic behind it that you want including, dated files, specialized locations, copying to multiple locations, and more. Once you’ve got that in a stored procedure, the sky’s the limit.
That’s why I put this in the system backup row though. Because it doesn’t need to be backed up with all of your other databases, and while I think even backing it up every night may be overkill, it’s a very small level of overkill that will only do good in the world.
I’ve also uploaded this solution to the Community Zone. And while you’re there go ahead and check out all the other cool scripts your fellow Minion users have uploaded. Here’s the direct link to the download page to Backup the ResourceDB with Minion Backup.
Download FREE Minion maintenance modules below: