The resource database (mssqlsystemresource) database is the new system databases available in SQL Server 2005 and above. Here is some information about the resource database.
- This hidden database stores system stored procedures and functions
- This was created by Microsoft in order to facilitate faster upgrades. If the system objects were to be upgraded in any Service Pack, just replacing the resource database is enough.
- This database does not list when sp_helpdb is executed.
- The location of mssqlsystemresource.mdf/.ldf files are different for SQL Server 2005 & SQL Server 2008.
- In SQL Server 2005 these files are located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ folder. These files must be in the same folder where the data files of master database are stored.
- In SQL Server 2008 the default location of these files is C:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn. These files need not be on the same directory where master database files are located.
- Since resource database is hidden, it cannot be backed up using the Backup Database command. The only way to backup this database is to copy the .mdf & .ldf files. Same holds good for restoring this database. If resource database is restored, all the service packs/hotfixes need to be reapplied.
- The database id 32767 is reserved for resource database and cannot be changed. If in SQL Server 2000, if any database is allocated the ID 32767, the upgrade to SQL Server 2005 will fail.