Move all SQL Server system databases at one time

By:   |   Updated: 2011-09-05   |   Comments (8)   |   Related: More > System Databases


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 Server 2000 system databases is much more convoluted than moving SQL Server 2005, 2008 or 2008R2 databases.

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, SQL Server 2008 or SQL Server 2008R2 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 locations 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.  Note you cannot move the mssqlsystemresource database for SQL Server 2008 or SQL Server 2008R2.

  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 locations 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 using same path as the master database
  7. Move the MDF and LDF files to the 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, 2008 and 2008R2 is much easier than moving the databases for SQL Server 2000 especially if you do not move the mssqlsystemresource database.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2011-09-05

Comments For This Article




Tuesday, November 3, 2015 - 6:12:55 AM - Kev Di Sotto Back To Top (39008)

Something to add, is to make sure that the SQL server service account has access to where you move the new files. If not, SQL will not start backup.

Thursday, December 12, 2013 - 5:57:59 PM - Greg Robidoux Back To Top (27785)

Hi adame, take a look at this article.  I think this is what you are looking for: http://yrushka.com/index.php/database-recovery/sql-server-migration-from-one-server-to-another-detailed-checklist/

 

Thursday, December 12, 2013 - 12:43:21 PM - adame Back To Top (27784)

Greg-

Thanks for the response.  The new server is still in the box to add but looking to move and upgrade the whole SQL 2008 environment.

Wednesday, December 11, 2013 - 3:19:33 PM - Greg Robidoux Back To Top (27770)

Hi Adame,

are you referring to moving the user databases or the system databases to the new server?

Wednesday, December 11, 2013 - 2:38:46 PM - adame Back To Top (27768)

I am upgrading from SQL 2008 to SQL 2008 R2 and also moving the database to a brand new server as well.  Is it best to upgrade the old server to 2008 r2 first or can this be done in one process?

 

Thursday, April 29, 2010 - 4:56:01 PM - admin Back To Top (5301)

I jusr came across this for SQL 2008

http://msdn.microsoft.com/en-us/library/ms345408.aspx 

Moving the Resource Database

In SQL Server 2008, the location of the Resource database is <drive>:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\. The database cannot be moved.

 

For SQL 2005 I am pretty sure you need to do use the ALTER DATABASE command.

Thursday, April 29, 2010 - 1:12:31 PM - wshawnmelton Back To Top (5300)

The only thing required to move the Resource database is to copy the MDF and LDF files to the same location of the master.mdf file.  The alter database command does not have to be executed against that database, and I don't believe you can execute it against that database.

 http://msdn.microsoft.com/en-us/library/ms190940%28SQL.90%29.aspx


Wednesday, March 3, 2010 - 4:29:02 PM - MikeL Back To Top (5000)

 I have a sql server 2005 64 bit 2 node cluster. I tried to move master following actually a page that you reference and all works until I attempt to move the resource database. I stop sql server service from configuration manager and then run:

 D:\>NET START MSSQLSERVER /f /T3608
The SQL Server (MSSQLSERVER) service is starting.
The SQL Server (MSSQLSERVER) service was started successfully.

 

Next I try to log in using sqlcmd I get this:

D:\>D:\MSSQL\90\Tools\binn\sqlcmd.exe -sMSSQLSERVER
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

 

Now I have done used the instructions on moving sql server user and system databases on a non-clustered environment to test without this issue. I am thinking that I need to start sql server in a cluster differently but how do I get these recommended flags as part of the service startup? Using net start seems to have an issue when in a cluster.















get free sql tips
agree to terms