Important notice: Although querying SQL Server 2005 system tables / databases directly is not a recommended way to obtain an information about your SQL Server installation – sometimes you might found it useful or even the only way possible to execute your particular task. Anyway: system tables / databases should not be altered directly by any user, nor modified with DELETE, UPDATE or INSERT T-SQL statements. Therefore, you should remember, that updating, deleting, or inserting data in a system table could cause unpredictable results for your SQL Server system. Microsoft does not support such updates.
Hi friends! Do you know that currently you have more then 4 (5 – if your database server act as a replication distributor and therefore has distribution system database) system databases? If not – take a look at \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder and you will surprisingly find 2 files: mssqlsystemresource.mdf and mssqlsystemresource.ldf.
Tip: As you already know *.mdf file name extension by default used to point at a primary data file for any database (*.ndf – for secondary data file) and *.ldf - file name extension for log file. SQL Server 2005 does not enforce the *.mdf (*.ndf) and .ldf file name extensions, but these extensions help you identify the different kinds of database files and their use.
So, it seems this two files point to another one system database. If so - should we find it name under the Databases -> System Databases node in SQL Server Management Studio? Answer is – NO! Question 2: should we find an occurrence of this database name in a sys.sysdatabases catalog view? Answer is the same – NO! Run the following code snippet in a Management Studio:
select * from sys.sysdatabases
go
and you will discover that 8 database (depending of SQL Server components being installed) list in a “fresh” SQL Server 2005 installation. For example: look at how an output from an above T-SQL code looks like in my development environment:
The next question you logically will ask sounds this way – what tables / objects / data stored in this database and how to view them?
Digging in Books On-Line give me the following information about this “hidden” database (quotation from BOL): The Resource database is a read-only database that contains all the system objects that are included with SQL Server 2005. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server. Similarly, rolling back system object changes in a service pack only requires overwriting the current version of the Resource database with the older version.
Based on above quotation we have already knows this database name. Resource! Not a MSSQLSystemResource (as you probably trying to guess based on a file names of it’s primary / log file names)!
Now I will try to show you how to attach this database (to a Databases -> System Databases node in SQL Server Management Studio) and make it’s objects available for viewing:
1. Using Services applet stop the Microsoft SQL Server 2005 system service (SQL Server (MSSQLSERVER));
2. Copy mssqlsystemresource.mdf to mssqlsystemresource_COPY.mdf and mssqlsystemresource.ldf to mssqlsystemresource_COPY.ldf. By sure to leave original files intact (do rename or move them)!!!;
3. Start the SQL Server (MSSQLSERVER) system service;
4. Attach copy of the Resource database using the following T-SQL code:
EXEC sp_attach_db
'MSSQLServerResource_Copy',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSSQLSystemResource_copy.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MSSQLSystemResource_copy.ldf';
Pay attention – you can not attach a copy of the Resource database via SQL Server Management Studio. The error you will receive is shown below:
5. Now you have done:
Tips for SQL Server system resource database:
- The resource database depends on the location of the master system database. If you move the master database, you must also move the resource database to the same location
- Never place / move this database to a compressed NTFS disk / folder. Doing so will hinder performance and possibly prevent future upgrades;
- Never place / move this database to an encrypted NTFS disk / folder. Doing so will hinder performance and possibly prevent future upgrades;
- You can not backup resource database via SQL Server Management Studio and / or Transact-SQL code. Perform your own (manual or as a scheduled task) backup of two files: mssqlsystemresource.mdf and mssqlsystemresource.ldf. Restore operation is also could be done manually only.
Important notice: Although querying SQL Server 2005 system tables / databases directly is not a recommended way to obtain an information about your SQL Server installation – sometimes you might found it useful or even the only way possible to execute your particular task. Anyway: system tables / databases should not be altered directly by any user, nor modified with DELETE, UPDATE or INSERT T-SQL statements. Therefore, you should remember, that updating, deleting, or inserting data in a system table could cause unpredictable results for your SQL Server system. Microsoft does not support such updates.
