This one doesn’t come up that often for me.  Since it doesn’t I feel I need to blog about it as an aid to memory and to anyone else who stumbles across it.

So, if you need to move your system databases as part of a routine operation and to a location on the same server/instance, it is actually a surprisingly easy and easy to figure out process.  The whole thing end to end falls into the following steps.

  1. Modify startup options -d and -l to point to the new location of your Master database data and log files.
  2. Alter the file locations of the remaining SQL Server system databases (model, tempdb, msdb etc.).
  3. Stop the SQL Server service.
  4. Move the data and log files to their new homes.
  5. Start the SQL Server service.

For those a little newer to this sort of operation, I’ll detail the first two steps.  The last three shouldn’t really need covering here.

  1. Modify startup options -d and -l to point to the new location of your Master database data and log files.
  • Open SQL Server Configuration Manager
  • In the left pane, select SQL Server Services
  • In the right pane right click > properties on SQL Server (INSTANCENAME)
  • On the Startup Parameters tab select the line with the -d switch, edit the parameter in the box above with the new Master database data file and click Update.  Do the same for the log file, using the parameter with the -l switch.

2.  Alter the file locations of the remaining SQL Server system databases (model, tempdb, msdb etc.).

  • For each data and log file associated with each of the system databases you want to relocate, simply run the following command, replacing with your own pertinent data.  Note that NAME is the logical database name.
ALTER DATABASE msdb
MODIFY FILE ( NAME = MSDBData, FILENAME = 'G:\system_db\MSDBData.mdf'); GO

ALTER DATABASE msdb
MODIFY FILE ( NAME = MSDBLog, FILENAME = 'G:\system_db\MSDBLog.ldf'); GO

Before moving on to later steps, you can check to make sure the locations have updated correctly by querying sys.master_files and checking the physical_name column.

BOL article can be found here.

Advertisements