How to backup the SQL Server SystemResource db – mssqlsystemresource

22 February,2017 by Jack Vamvas

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)

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to backup the SQL Server SystemResource db – mssqlsystemresource


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer