Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped with SQL Server. 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.
Resource database will be very useful in doing upgrades or in un-installing up-grades. In the previous versions of SQL Server up-grade needs creating/dropping of the system objects. From the SQL Server 2005 version upgrade is just procedure to copying resource database file to local server.
Important : :Although it was possible to move the Resource database in SQL Server 2005, things changed in SQL Server 2008 where the location of the Resource database can no longer be relocated from its default directory. In SQL Server 2005, the Resource database depended on the location of the master database. The Resource data and log files had to reside together and had to be in the same location as the master data file (master.mdf). Therefore, in SQL Server 2005, if you moved the master database, you had to also move the Resource database to the same location as the master data file. In SQL Server 2008, the location of the Resource database cannot be moved from its default location.
For example, in SQL Server 2008 R2, the Resource database is located under the directory:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn mssqlsystemresource.mdf mssqlsystemresource.ldf
Name of Resource database data and log file.
mssqlsystemresource.mdf
mssqlsystemresource.ldf
Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.
You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.
1
2
3
4
SELECT SERVERPROPERTY(‘RESOURCEVERSION’);
GO
SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);
GO
To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.
You can do it either from the Configuration manager or from the command prompt using below command.
Default Instance
NET START MSSQLSERVER /f /T3608
Named Instance
NET START MSSQL$instancename /f /T3608
Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.
1
2
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')