SYSK 328: The “Hidden” System Database in SQL 2005

If you just open SQL Server Management Studio and expand the System Databases node, you’ll see four databases – master, model, msdb and tempdb (just like in SQL 2000).

 

However, SQL Server 2005 has a new system database called the ‘Resource’ database, which contains all the system objects (see select * from sys.objects), which appear in the INFORMATION_SCHEMA of each database but are actually stored in the Resource database. No user data or user metadata is stored in the Resource database.

 

The Resource database is read only for users, i.e. you can’t insert or update data at will.

 

I’m not aware of the reason, but baking up master database doesn’t automatically backup the Resource database (to me that would make sense). Moreover, you cannot backup the Resource database by using BACKUP DATABASE statement. Instead, you should copy it as a file to your backup share.

 

The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. By default, these files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file. In a cluster, the Resource database exists in the \Data folder on a shared disk.

 

 

Source: http://msdn2.microsoft.com/en-us/library/ms190940.aspx

 

Special thanks to Saleem Hakani for bringing this information to my attention!