How to Move MSDB & Model SQL Server system Databases ?

Steps of Moving MSDB & Model SQL Server system Database to new locaation:-

1) Check current location of MSDB & Model Databases by executing below query

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id in (DB_ID(‘MODEL’),DB_ID(‘MSDB’));

You can also use “Execc SP_HelpDB ‘<DBNAME>’” for these details.

2) Execute Alter Database command with mofigy file option to set file loccation for dataabase

USE MASTER;

GO

ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBDATA,

FILENAME=’E:\SQL2K5_1\Model\MSDBDATA.mdf’);

GO

ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBLOG,

FILENAME=’E:\SQL2K5_1\Model\MSDBLOG.ldf’);

GO

USE MASTER;

GO

ALTER DATABASE MODEL MODIFY FILE (NAME = MODELDEV,

FILENAME=’E:\SQL2K5_1\Model\MODEL.mdf’);

GO

ALTER DATABASE MODEL MODIFY FILE (NAME = MODELLOG,

FILENAME=’E:\SQL2K5_1\Model\MODELLOG.ldf’);

GO

OUTPUT :-

The file “MSDBDATA” has been modified in the system catalog. The new path will be used the next time the database is started.

The file “MSDBLOG” has been modified in the system catalog. The new path will be used the next time the database is started.

The file “MODELDEV” has been modified in the system catalog. The new path will be used the next time the database is started.

The file “MODELLOG” has been modified in the system catalog. The new path will be used the next time the database is started.

3) Stop SQL Services

4) Manually Move MSDB & Model Databases files to new location

5) Start SQL Services

6) Check MSDB & Model Databases file location after movement

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