How to Move your systemDB's in SQL server 2005

Ref Link ->http://support.microsoft.com/kb/224071

1. Identify you have good backup in place if not take it.

2. Identify No jobs are running and no users connected to system db & user DB’s

3. Identify the Physical location of the files of MSDB and Model.

Ex->In my test server as mentioned below-

modeldev           1              C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf

modellog             2              C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf

MSDBData          1              C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf

MSDBLog             2              C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf

4.We need to use SCM to set up below parameters

-c -m -T3608

-T3608 : Trace flag 3608 prevents SQL Server from recovering any database except the master database.

Apply->OK then you will see below warning ->click on OK

Click OK.

5.Stop the SQL server service and SQL server Agent.

6. Start the SQL server service  only (Do not touch SQL agent –let is stop state)again

Once the SQL server services started then go for the step 7

7. Connect to the SSMS->verify you have connected to the SSMS (I,e you can see you have connected)

Like try to connect with other user  using ->Connect to database engine using object  Explorer

 you will get the below error as mentioned below-

8.Disconnect the your session from the SSMS using Object explorer

Now you can see below screenshot

Shows nothing visible

9.Then in GUI ->click on new query

You will see your session opens with the below screenshot-

10. Execute the below query as mentioned below

use master

go

sp_detach_db 'msdb'

go

the output will be as mentioned below –

11. Since I have only one C:Drive ,hence I have used to move the physical files to  some other folder

In the same drive I,e  I plan to move C:\MSDB Move.

So I have moved the files to the above location->After moving the physical file from current to

 C:\MSDB Move

Output will be

12.Next go to the SCM(SQL server Configuration Manager) remove the startup parameters

Apply ->OK

Same warning you will get 

Click OK

13.Stop the SQL server  service

14.Start SQL server service .

15. Execute the below query to attach

use master

go

sp_attach_db 'msdb','C:\MSDB Move\msdbdata.mdf','C:\MSDB Move\msdblog.ldf'

go

after executing you will see otput below

16.Verify MSDB is up and the Physical file reflected as mentioned below-

17.Go to SQL server errorlog and event logs no errors found.

18.Change the DBowner of MSDB

USE MSDB

Go

EXEC sp_changedbowner 'sa'

Go

NOTE =>Do not start the SQL server agent until the MSDB comes online other wise you will get error like,

In the event logs

Event Type:        Error

Event Source:    MSSQL$SQL2K5

Event Category:                (2)

Event ID:              8355

Date:                     12/10/2011

Time:                     22:14:55

User:                     N/A

Computer:          RAKUBERA

Description:

Service Broker is disabled in MSDB or MSDB failed to start. Server level event notifications can not be delivered. Event notifications with FAN_IN in other databases could be affected as well.

You will get errors as timely fashion the SQL server agent not able to start please see the event logs

So similarly you can do for MODEL database as well.