SQL Server system database – mssqlsystemresource
February 25, 2012 1 Comment
The database mssqlsystemresource is a read-only database that is shipped with SQL Server from version SQL Server 2005. It contains all the system objects that are included in SQL Server. An example of this are the sys.objects. These are stored in the Resource database, but appear in every other database. The sys.objects from a user database refer to the Resource database.
SQL Server cannot backup this database. You can create a backup yourself, but don’t back it up like a normal .MDF file. It also can’t be restored via SQL Server, but you can do it manually.
The Resource database makes upgrading to a new version easier and faster.
The mssqlsystemresource database is invisible for users, even for the System Administrator (sa). The database can be restored by copying the .ldf and .mdf files from the folder “[Drive]\Program Files\Microsoft SQL Server\MSSQL10_50.[Instance Name]\MSSQL\Binn” to a folder of your choice (in this case D:\Databases). After that, start a query with the user sa, and run the following script:
USE [master] GO CREATE DATABASE [mssqlsystemresource_RESTORED] ON (FILENAME = N'D:\Databases\mssqlsystemresource.mdf'), (FILENAME = N'D:\Databases\mssqlsystemresource.ldf') FOR ATTACH GO
Once you’ve done this, you can query the restored version of the Resource database.