Home » SQL Server » How to move Resource DB in SQL Server ?

How to move Resource DB in SQL Server ?


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

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

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

http://beyondrelational.com/members/RohitGarg/default.aspx

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: