How to Backup Resource Database
SQL Server does not allow you to take the backup of the Resource Database using Transact SQL code or by using SQL Server Management Studio. In order to take the backup of Resource Database you need to perform a file based or a disk based backup of the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files, by treating the files as if they were like any other binary files, instead of a database file. The important thing to be noticed is that SQL Server will allow Resource Database related MDF & LDF files to be copied even when SQL Server is running. When the SQL Server Service is running it will only allow the Resource Database related MDF and LDF files to be copied, however it will not allow you to copy any other databases MDF or LDF files.
How to Restore Resource Database
SQL Server does not allow you to restore Resource Database using Transact SQL code or by using SQL Server Management Studio. The only way to restore the Resource Database is to copy the Mssqlsystemresource.mdf and Mssqlsystemresource.ldf files to the respective folders. As part of the disaster recovery process it is very important for a DBA to document the location of Master and Resource databases. In a scenario when a DBA decides to rebuild the SQL Server 2005 or SQL Server 2008 instance, the DBA needs to manually copy the Resource Database to the respective folder locations prior to the restore of Master Database. If you don’t have the latest backup of Resource Database then you need to reapply all the subsequent updates before you can bring you environment back to what it was prior to disaster. So the best approach is to daily take backups of the system and user databases.
Conclusion
It’s very important for a DBA to include the Resource Database as part of the Disaster Recovery Document and they should ensure that Resource Database is part of daily back up plan along with other system and user databases.
]]>