Systems DB Move

Question

Systems DB Move

TRACEY-320982

SSChampion

Points: 13653
More actions
January 16, 2009 at 9:13 am

#125595

All system database master, model, msdb are on C: i want to move all to D.
What the best method to following in moving.
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

this is the method that I use and Microsoft recommends;

http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx"> http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

A quick google search brings up the following page:

http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jason Crider SSCertifiable Points: 6019 More actions · Answer 3

I had to do this myself and it was a beast because I had not properly researched it.

Here's my post from that night about one of the issues.

I wish I would have documented it a little better. There's one other issue I ran into with SSIS and if I find it I will post it.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

I don't see your steps in the post ?

Yes i don't want to spend hours on it he he.....i got three instances of system db to move around to all difference disks, as they are currently all on C

Can you list for me thanks.

steveb. SSC-Forever Points: 46733 More actions · Answer 5

TRACEY (1/16/2009)
I don't see your steps in the post ?
Yes i don't want to spend hours on it he he.....i got three instances of system db to move around to all difference disks, as they are currently all on C
Can you list for me thanks.

have you checked the links that were provided ? , they pretty much give you a step by step guide..

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 6

The thread seems rather confusing to me.........

Going to start with master first.

1.

....So i start by going to the configuration tool and set path from C: TO D:

-dD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;

-eD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;

-lD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2. Stop SQL

3. Move master.mdf and master.ldf from C: to D:

4. in Dos NET START MSSQL$instancename /f /T3608

5. Use sqlcmd and do step 8 Got confused what this mssqlstemresource.mdf is ?

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');

GO

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');

6. Do the readonly for mssqlsystemresource ?

7. Exit SQLCMD

8. Stop SQL

8. Restart SQL in normal mode

9. Check locations.

GO

Perry Whittle SSC Guru Points: 234013 More actions · Answer 7

Tracey

remember the path for the resource db data and log file must be the same as the master.mdf

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 8

So with the below:

I don't have to do a alter command for master this is taken care of by doing the

configration setting.

1.

....So i start by going to the configuration tool and set path from C: TO D:

-dD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;

-eD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;

-lD:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

2. Stop SQL

3. Move master.mdf and master.ldf from C: to D:

4. in Dos NET START MSSQL$instancename /f /T3608

5. Use sqlcmd and do step 8 Got confused what this mssqlstemresource.mdf is ?

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');

GO

ALTER DATABASE mssqlsystemresource

MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');

6. Do the readonly for mssqlsystemresource ?

7. Exit SQLCMD

8. Stop SQL

8. Restart SQL in normal mode

9. Check locations.

GO

Once i got the master working, the msdb, model i can do how?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

TRACEY (1/16/2009)
Once i got the master working, the msdb, model i can do how?

See the link that Steve and I posted. It has sections for all of the system databases (starting with msdb and model)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 10

Yes im following the link and just doing the english version...so does the above look right to you.

Then i can start moving things around..

Cheers

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 11

Have a look at http://www.sqlservercentral.com/articles/Administration/2605/

MJ

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 12

I just uninstalled it all and reinstalled SQL to the correct paths .....