Tag Archives: Resource Database

Steps to restore the resource database

Few days back, I had discussion with my team member regarding the resource database and we all are curious to see the resource database.

As you all know resource database is hidden database and we can’t see it SQL Server. We follow below steps to restore the resource database and it worked. We can see the resource database.

Resource database file location
By default, these files are located in :\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.

Follow below steps if you want to see the resource database.
Step 1: Copy the resource database MDF/LDF file to different location and rename it. We have name both data and log file to resourcetest.

Step 2: Execute the below command to attach the resource database file and you can see the resource database.

USE [master]
GO
CREATE DATABASE [resourcetest] ON 
( FILENAME = N'J:\resourcetest.mdf' ),
( FILENAME = N'J:\resourcetest.ldf' )
 FOR ATTACH
GO

Resource Database Image

How to Move Resource Database?

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.

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.

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.

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')