Recovering/Restoring the Resource system database in SQL Server 2005

Here is the scenario I have been working through in the LAB today:

The Resource database (mssqlsystemresource) has become corrupt on a SQL 2005 SP4 instance. You are not sure why the database has become corrupt but when you try and start the SQL Server DB Engine the following is reported in the SQL Server ERRORLOG:

2011-08-01 14:57:23.89 spid5s Starting up database 'mssqlsystemresource'.
2011-08-01 14:57:23.91 spid5s Error: 5172, Severity: 16, State: 15.
2011-08-01 14:57:23.91 spid5s The header for file '\MSSQL.1\MSSQL\DATA\mssqlsystemresource.mdf' is not a valid database file header. The PageAudit property is incorrect.
2011-08-01 14:57:23.95 spid5s Error: 945, Severity: 14, State: 2.
2011-08-01 14:57:23.95 spid5s Database 'mssqlsystemresource' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-08-01 14:57:23.97 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.



We know that we cannot backup/restore the Resource database using SQL Server (as per http://msdn.microsoft.com/en-us/library/ms190940%28v=sql.90%29.aspx) and you could restore the physical file from the server's file system backups. Unfortunately though the file is unable to be recovered from the server's file system backup for an unknown and unrelated external factor.

Never fear there is a simple fix to this. Reinstall the Service Pack as that will recreate the Resource database.... Hurray

Here's the steps and things to note when performing the Service Pack install though:
1. Launch the Service Pack install
2. Accept the terms and conditions and click Next
3. When asked to select the Feature to update you will notice that the Database Services for the required instance will already report "Upgraded". Simply select the instance though as this will re-apply the service pack.
4. Complete the service pack install

Once the Service Pack has been installed you should be able to start the SQL Server Service for the required Instance.

TIPS: You can use the following TSQL to grab information about your SQL Instance and Resource Database. These were taken from http://msdn.microsoft.com/en-us/library/ms190940%28v=sql.90%29.aspx

SELECT @@VERSION
SELECT SERVERPROPERTY('ResourceVersion');
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');


SQL 2008 / SQL 2008 R2
For the process to rebuild the Resource database in SQL 2008 follow the instructions at http://msdn.microsoft.com/en-us/library/dd207003.aspx

Happy Disaster Recovering everyone :)