Home MS SQL Server DB Maintenance How to move SQL Server system databases
How to move SQL Server system databases PDF Print E-mail
Written by Administrator   
Monday, 04 May 2009 10:12

Simplified process to move all SQL Server system databases at one time

Written By: Edgewood Solutions Engineers -- 10/14/2008

 Problem
One task that you may need to do as a DBA is to move the system databases from one location to another.  The documentation that is found on the Microsoft site is helpful, but the steps are more geared toward moving one database at a time.  In this tip we look at a streamlined process of moving all system databases at the same time.

Solution
There are few Microsoft KB articles that show you step by step on how to move the SQL Server system databases from one location to another location.  The steps for moving SQL Server2000 system databases is much more convoluted than moving SQL Server 2005 databases.  I have not had the chance to do this for SQL Server 2008, but my guess is that the process is just as easy as it is in SQL Server 2005.

The following KB articles show you step by step how to move the system databases, but the process does not need to be as long as the details show.

Moving all SQL Server 2005 System Databases

Once you have reviewed the KB articles above, you can follow these steps to move all system databases at once.

  1. Update the -d and -l startup parameters for SQL Server for the new location of the master data and log file
  2. Issue ALTER DATABASE commands to change the file location for the model, msdb and tempdb database files
  3. Stop SQL Server
  4. Move the MDF and LDF files to the new location specified in steps 1 and 2 for the master, model and msdb databases
  5. Start SQL Server
  6. Delete the old tempdb files

In addition to the master, model, msdb and tempdb databases SQL Server 2005 introduces the mssqlsystemresource database.  Microsoft recommends not moving this database, but if you do want to move this database as well you will follow these steps.

  1. Update the -d and -l registry startup parameters for SQL Server for the new location of the master data and log file
  2. Issue ALTER DATABASE commands to change the file location for the model, msdb and tempdb database files
  3. Stop SQL Server
  4. Move the MDF and LDF files to the new location specified in steps 1 and 2 for the master, model and msdb databases
  5. Put SQL Server in minimal configuration mode by adding these two startup parameters -f and -T3608 and then start SQL Server
  6. Issue ALTER DATABASE commands for the mssqlsystemresource MDF and LDF files
  7. Move the MDF and LDF files to the new location specified in step 6 for the mssqlsystemresource database
  8. Stop SQL Server
  9. Remove the startup options added in step 5
  10. Start SQL Server
  11. Delete the old tempdb files

Moving all SQL Server 2000 System Databases

Once you have reviewed the KB articles above, you can follow these steps to move all system databases at once.

  1. Update the -d and -l startup parameters for SQL Server for the new location of the master data and log file
  2. Issue ALTER DATABASE commands to change the file location for the tempdb database files
  3. Stop SQL Server
  4. Move the MDF and LDF files to the new location specified in steps 1 for the master database
  5. Put SQL Server in single user mode by adding these three startup parameters -c, -m and -T3608 and then start SQL Server
  6. Detach the msdb and model databases
  7. Move the MDF and LDF files to the new location for the model and msdb databases
  8. Attach the model database from its new location
  9. Stop SQL Server
  10. Remove the startup options added in step 5
  11. Start SQL Server
  12. Attach the msdb database
  13. Delete the old tempdb files

Although it still seems like a lot of steps this will cut down on the need to stop and start SQL Server as much if you move one database at a time.  As you can see moving all of the databases for SQL Server 2005 is much easier than moving the databases for SQL Server 2000 especially if you do not move the mssqlsystemresource database.

Next Steps