Moving the system databases in SQL Server 2000

The following post provides step by step instructions for moving each of the SQL Server 2000 system databases, one by one, from one location to another, on the same server.  These system databases being:

• master
• model
• msdb
• tempdb

In this case, I need to move all of these system databases from E:\MSSQL\MSSQL\Data to D:\MSSQL\Data

For the model and msdb databases, the order of reattachment is critical, as moving msdb first will present problems…trust me! 😉

Master database

Update the -d and -l startup parameters for your SQL Server instance with the new location for your master MDF and LDF files (do not move any files at this stage).  To do this, open SQL Server Enterprise Manager, right-click the server name and click Properties.

Open up the Startup Parameters, then remove the existing master MDF and LDF entries:

-de:\MSSQL\MSSQL\Data\master.mdf
-le:\MSSQL\MSSQL\Data\mastlog.ldf

..and add the new file locations:

-dD:\MSSQL\Data\master.mdf
-lD:\MSSQL\Data\mastlog.ldf

Click OK to confirm the changes.

NOTE: Moving the error log file is optional, I haven’t done it in this scenario.

-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.

Stop SQL Server (from a Command Prompt):

net stop SQLSERVERAGENT
net stop MSSQLSERVER

Move the master.mdf and mastlog.ldf files from E:\MSSQL\MSSQL\Data to the new location at D:\MSSQL\Data.

Sart SQL Server (from a Command Prompt) leaving the SQL Server Agent for now:

net start MSSQLSERVER

TempDB database

This one is more straight forward as the tempdb files are recreated at startup…

Check the logical names, current locations of the tempdb files etc:

USE tempdb
GO
EXEC sp_helpfile
GO

Then issue ALTER DATABASE SQL commands to change the file location of the tempdb database files:

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'tempdev', FILENAME = 'D:\MSSQL\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog', FILENAME = 'D:\MSSQL\Data\templog.ldf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = 'templog2', FILENAME = 'D:\MSSQL\Data\templog2.ldf');
GO

Restart SQL Server:

net stop MSSQLSERVER
net start MSSQLSERVER

Now you can delete the old tempdb files (you don’t need to move them as they’re recreated in the new location at startup).

model and msdb databases (model first and then msdb)

Add the -T3608 startup parameter to the instance:

Navigate to the msdb database properties, then select the Options tab.

Then restrict access to the msdb database by placing it into single-user mode first:

Now do the same for the model database:

Both the model and msdb databases should have ‘(Single User)’ appended to them when viewed in SQL Server Enterprise Manager now:

Restart SQL Server:

net stop MSSQLSERVER
net start MSSQLSERVER

You will not be able to access any user databases at this time, and you should not perform any operations other than the steps below while this trace flag is set.

Open SQL Query Analyzer to detach the model, then msdb databases the using the following commands:

USE master
GO
sp_detach_db 'model'
GO
sp_detach_db 'msdb'
GO

Move the MDF and LDF files for the msdb and model databases to the new location.

Attach the model and msdb databases (in that order*) using its new location file locations:

USE master
GO
sp_attach_db 'model','D:\MSSQL\Data\model.mdf','D:\MSSQL\Data\modellog.ldf'
GO
sp_attach_db 'msdb','D:\MSSQL\Data\msdbdata.mdf','D:\MSSQL\Data\msdblog.ldf'
GO

* the order of reattachment is important here – you must deal with model first and then msdb, otherwise you’ll run into problems.

Go back to SQL Server Enterprise Manager and remove the -T3608 startup parameter.

Finally, restart SQL Server and bring up the SQL Server Agent:

net stop MSSQLSERVER
net start MSSQLSERVER
net start SQLSERVERAGENT

You can check the locations of the system database files at any time by running:

USE <db_name>
GO
EXEC sp_helpfile
GO

Hope this is of use to people still running SQL Server 2000!