Wednesday, July 16, 2008

You need to backup the mssqlsystemresource database files

So, you think you have all the necessary database backups you need? Well, think again. This might be your mindset after moving from SQL Server 2000 to SQL Server 2005. SQL Server 2005 includes a read-only, hidden database that contains all the system objects. It comes in the form of mssqlsystemresource.mdf and mssqlsystemresource.ldf files located in :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ by default. This database is required by the master database and will definitely affect how your SQL Server service behaves during restarts. If you move the master database, you should also move these files no the same location. Since it is a hidden database, SQL Server won't be able to backup this database as part of your database maintenance plan. One approach you could use is to xcopy it to the location of your database backups on a regular basis.

MSDN has a documentation on what this is all about but let me tell you one thing - you MUSt include this in your backup procedures. If you want to have an idea of how critical these files are, stop your SQL Server 2005 service and rename the mssqlsystemresource.mdf file. You won't be able to restart your SQL Server service afterwards

No comments:

Google