How to move the system datafiles (tempdb, master. msdb,...) to a diff drive(d$ -> E$)

Question

Post reply

How to move the system datafiles (tempdb, master. msdb,...) to a diff drive(d$ -> E$)

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
May 12, 2010 at 11:54 am

#143057

Hi Experts
Please provide the process (in steps) to move all the systrem database data and log files from D:\ drive to E:\ drive because D:\ drive has got a less space due to which the shrinking procedure failing.
The SQL Server is SQL 2000.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Here are the instructions.

http://support.microsoft.com/kb/224071

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

Hi

For SQL Server 2005 move the master first using the following

Edit the SQL server service and change the paths for the MASTER data and log files to the new location. The resource DB mdf and log must be in the same location as master.mdf

Stop the sql instance and agent and copy (not move) the database files for master, model, msdb, mssqlsystemresource and dist DB files to their new locations.

Start the sql instance from a command window on the server using the following

NET START MSSQLSERVER /f /T3608 --default instance

NET START MSSQL$instancename /f /T3608 --named instance

Set the MSSQLSYSTEMRESOURCE DB paths using the following T-SQL via a SQLCMD shell

syntax is

SQLCMD -Sserver\instancename

Alter the database paths using

!!!!DO NOT change any filenames only the paths, you have been warned!!!!!

ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = data ,

FILENAME = 'E:\Newpath\mssqlsystemresource.mdf' )

ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = log ,

FILENAME = 'E:\Newpath\mssqlsystemresource.ldf' )

--then run

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

--stop the sql instance from the same command window using

NET STOP .........

Restart SQL instance normally but not agent, now set the path of each file in the remaining system databases

ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev ,

FILENAME = 'E:\Newpath\tempdb.mdf' )

ALTER DATABASE tempdb MODIFY FILE ( NAME = templog ,

FILENAME = 'E:\Newpath\templog.ldf' )

ALTER DATABASE model MODIFY FILE ( NAME = modeldev ,

FILENAME = 'E:\Newpath\Data\model.mdf' )

ALTER DATABASE model MODIFY FILE ( NAME = modellog ,

FILENAME = 'E:\Newpath\modellog.ldf' )

ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata ,

FILENAME = 'E:\Newpath\msdbdata.mdf' )

ALTER DATABASE msdb MODIFY FILE ( NAME = msdblog ,

FILENAME = 'E:\Newpath\msdblog.ldf' )

After these paths have been set, restart the SQL instance and its agent.

Clean up by removing the now unused database files for each system database on the old path.

Ooh, and don't forget to put in an RFC first and get this through CAB before making the changes 😉

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Perry Whittle (5/12/2010)
Hi
For SQL Server 2005 move the master first using the following

This is good info for when it is 2005. OP is looking for 2000.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

Thank Perry Whittle. However, will the above sugestion work file for SQL Server 2000?

Thanks.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Sourav-657741 (5/12/2010)
Thank Perry Whittle. However, will the above sugestion work file for SQL Server 2000?

For SQL Server 2000, check out the article I posted. It has the step by step instructions.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

Sourav-657741 (5/12/2010)
Thank Perry Whittle. However, will the above sugestion work file for SQL Server 2000?

no, i would try posting in the sql server 2000 forum in the future 😉

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

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