How to backup the SQL Server SystemResource db – mssqlsystemresource

Question: I’ve got a sql backup script which completes a backup of all sql server databases on a SQL Server. This should include user and system databases. In reviewing the databases backed up , I can’t see a reference to the mssqlsystemresource database.

Am I missing something or is it not possible to backup via a sql database backup script through the standard sql BACKUP command?

Answer: You can’t use the BACKUP command on the mssqlsystemresource db. You’ll need to take a flat file backup of the files , and in the event of recovery restore the files to their original location.

If the original paths were as below, than they need to be replaced to those locations. Otherwise on a SQL Server start there will be a failure.

C:\Program Files\Microsoft SQL Server\MSSQL11.instance1\MSSQL\Binn\mssqlsystemresource.mdf

C:\Program Files\Microsoft SQL Server\MSSQL11.instance1\MSSQL\Binn\mssqlsystemresource.ldf

A common problem is server flat file backups excluding mdf and ldf files . As a consequence on a server corruption and restore of all files – the mssqlsystemresource files are not recovered. The simple solution is to include these files.

It is always worth  testing your recovery procedures as realistically as possible.  Not being able to recover databases  is a critical problem.

Read Resource database in SQL Server SystemResource db - mssqlsystemresource  for how to find the physical file using t-sql

Read More

Schrodinger's backup - A warning for DBAs (SQL Server DBA)