The Resource database is a read-only database that contains all the system objects. This DB included with SQL Server 2005 to increase security of system metadata. 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. You cannot access Resource database directly, it’s a hidden database & accessible through system catalog only.
Note :
1) Resource & Master Database location must be same.
2) From SQL Server 2008 onwards Resource DB resides in BIN folder & cannot be moved.
Steps to Move Resource Database :-
1) Start in master-only recovery mode by running:
NET START MSSQLSERVER /f /T3608
You can also do it from configuration manager, Like we start SQL server in single user mode. (http://mssqlfun.com/2014/09/04/how-to-start-sql-server-in-single-user-mode/)
2) Start a command prompt and run SQLCMD.
3) Use the ALTER DATABASE statement with the MODIFY FILE option to specify the new location for the resource database data and log files.
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= ‘E:SQL2K5_1Mastermssqlsystemresource.mdf’);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= ‘E:SQL2K5_1Mastermssqlsystemresource.ldf’);
GO
4) Use the ALTER DATABASE statement to make the Resource database read-only.
Alter Database mssqlsystemresource set Read_only;
5) Stop the SQL Server service.
6) Move the database files for the mssqlsystemresource database to new location.
7) Start the SQL Server service.
Reference : Rohit Garg (http://mssqlfun.com/)
You can find and follow MSSQLFUN :-
http://www.facebook.com/mssqlfun
Other Linked Profiles :-
http://www.sqlservercentral.com/blogs/mssqlfun/
http://social.msdn.microsoft.com/Profile/rohitgarg
http://www.toadworld.com/members/rohit-garg/blogs/default.aspx
