Home » SQL Server » How to Move MSDB & Model SQL Server system Databases ?

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\model.mdf’);

GO

ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBLOG,

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

GO

USE MASTER;

GO

ALTER DATABASE MODEL MODIFY FILE (NAME = MODELDEV,

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

GO

ALTER DATABASE MODEL MODIFY FILE (NAME = MODELLOG,

FILENAME=’E:\SQL2K5_1\Model\MSDBLOG.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

Advertisements

2 Comments

  1. Conray J Forrester says:

    This query has serious errors in it, The database files for the MSDB and model database are switched around in the relocation. This can result in unaccessibility to the db server

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: