Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Moving System Databases - A Checklist

By Christoffer Hedgate,

Moving System Databases

Moving a database in SQL Server is normally as simple as detaching it from the server, moving it to it's new location and then attaching it again. However, that only counts for user databases. Moving system databases requires quite a bit more work. Since I had to do this a couple of times during the last weeks I wrote down a simple checklist that I could follow to make sure it was done right, so I did not screw up the servers and could speed up the process as much as possible. And, since I guess I will be doing it again sometime in the future and inevitably will lose the notes I scribbled down, I thought I could just as well post them here for safe-keeping. If anyone wants to use this checklist go ahead, but remember to do the steps in the exact order of the list, and make sure you have all the necessary backups before starting. To be completely clear, doing this wrong can completely screw up your databases and I can not take any responsibility if anything does go wrong. Also note that the checklist was written for the specific situation I was encountered with. Your system databases might have more data files and/or other file paths and names than those in the list, so you might need to make some changes. Have fun!

  1. Make sure you have backups of all user databases plus master, model and msdb.
  2. Moving msdb and model
    1. In Enterprise Manager, right-click the server and choose Properties.
    2. Click Startup Parameters.
    3. Add a new parameter "-T3608" (without the quotes)
    4. Stop SQL Server.
    5. Start SQL Server, and make sure that SQL Agent is NOT started.
    6. Run the following command in Query Analyzer:
      ------------- 
      use master 
      go 
      exec sp_detach_db 'msdb' 
      go 
      exec sp_detach_db 'model' 
      go 
      ------------- 
      				
    7. Move the data and log files for both msdb (normally msdbdata.mdf and msdblog.ldf) and model (normally model.mdf and modellog.mdf) to their new locations.
    8. Run the following in Query Analyzer:
      ------------- 
      use master 
      go 
      exec sp_attach_db 'model'
         , 'PATH_TO_MODEL_DATAFILE\model.mdf'
      	, 'PATH_TO_MODEL_LOGFILE\modellog.ldf' 
      go 
      ------------- 
      				
    9. Remove the -T3608 flag in Enterprise Manager/Server/Properties/Startup Parameters.
    10. Stop SQL Server.
    11. Start SQL Server.
    12. Run the following in Query Analyzer and check that the file paths are correct:
      ------------- 
      use model 
      go 
      exec sp_helpfile 
      go 
      ------------- 
      				
    13. Run the following in Query Analyzer:
      ------------- 
      use master 
      go 
      exec sp_attach_db 'msdb'
          , 'PATH_TO_MSDB_DATAFILE\msdbdata.mdf'
      	 , 'PATH_TO_MSDB_LOGFILE\msdblog.ldf' 
      go 
      ------------- 
      				
    14. Run the following in Query Analyzer and check that the file paths are correct:
      ------------- 
      use msdb 
      go 
      exec sp_helpfile 
      go 
      ------------- 
      				
    15. Finished!
  3. Moving tempdb
    1. Run the following in Query Analyzer:
      ------------- 
      use master 
      go 
      alter database tempdb modify file (name = tempdev
         , filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\tempdb.mdf') 
      go 
      alter database tempdb modify file (name = templog
         , filename = 'PATH_TO_NEW_LOCATION_OF_TEMPDB_DATAFILE\templog.ldf') 
      go 
      ------------- 
      				
    2. Stop SQL Server
    3. Start SQL Server
    4. Run the following in Query Analyzer and check that the file paths correspond to those stated in step 1:
      ------------- 
      use tempdb 
      go 
      exec sp_helpfile 
      go 
      ------------- 
      				
    5. Stop SQL Server.
    6. Move the original data and log files for tempdb to some new location, or rename them, just so that you are sure that SQL Server can not be using them.
    7. Start SQL Server.
    8. Run the following in Query Analyzer and check that no error occurs:
      ------------- 
      use tempdb 
      go 
      create table test (a int) 
      insert into test (a) values (1) 
      select * from test 
      drop table test 
      go 
      ------------- 
      				
    9. Remove the original data and log files for tempdb.
    10. Finished!

      NOTE: Steps 5 through 8 are of course not really necessary, they are just included as a quick extra check to really make sure that nothing went wrong. Skip them if you wish.
  4. Moving master

    Note: In this scenario we are not only moving the master database, we are also moving all of the files that SQL Server uses in it's 'data location' (as specified when installing SQL Server). The situation I encountered was that SQL Server's data location was specified to be something like D:\ (though with the program files as normal on C:\Program Files\Microsoft SQL Server\), but now the entire D:\ drive needed to be removed, so we needed to move everything SQL Server had stored there plus all references to it to avoid problems in the future. If you are only moving the master database you only need to follow the applicable steps of course.
    1. In Enterprise Manager, right-click the server and choose Properties.
    2. Click Startup Parameters.
    3. Remove all of the three parameters that are already there (if there are more, remove the three that correspond to the three below in step 4).
    4. Add the three following parameters:
      -dPATH_TO_NEW_LOCATION_OF_MASTER_MDFFILE\master.mdf
      -ePATH_TO_NEW_LOCATION_OF_SQLAGENT_ERRORLOG\ERRORLOG
      -lPATH_TO_NEW_LOCATION_OF_MASTER_LOGFILE\mastlog.ldf

      In my case the values of these parameters where as follows:
      -dE:\MSSQL\Data\master.mdf
      -eE:\MSSQL\LOG\ERRORLOG
      -lE:\MSSQL\Data\mastlog.ldf
    5. Stop SQL Server.
    6. Move the files as specified below:
      OLD_PATH_TO_MASTER_MDFFILE\master.mdf --> NEW_PATH_TO_MASTER_MDFFILE\master.mdf 
      OLD_PATH_TO_MASTER_LOGFILE\Data\mastlog.ldf --> NEW_PATH_TO_MASTER_LOGFILE\mastlog.ldf 
      OLD_PATH_TO_SQL_DATA_LOCATION\BACKUP --> NEW_PATH_TO_SQL_DATA_LOCATION 
      		(the entire folder with everything in it) 
      OLD_PATH_TO_SQL_DATA_LOCATION\JOBS --> NEW_PATH_TO_SQL_DATA_LOCATION 
      		(the entire folder with everything in it) 
      OLD_PATH_TO_SQL_DATA_LOCATION\LOG --> NEW_PATH_TO_SQL_DATA_LOCATION 
      		(the entire folder with everything in it) 
      OLD_PATH_TO_SQL_DATA_LOCATION\REPLDATA --> NEW_PATH_TO_SQL_DATA_LOCATION 
      		(the entire folder with everything in it) 
      			
    7. Make the following changes to the registry (using regedit):
      			HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer 
      		    BackupDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\BACKUP 
      
      		  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Replication 
      		    WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\REPLDATA 
      
      		  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup 
      		    SQLDataRoot = NEW_PATH_TO_SQL_DATA_LOCATION\ 
      
      		  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent 
      		    ErrorLogFile = NEW_PATH_TO_SQL_DATA_LOCATION\LOG\SQLAGENT.OUT 
      		    WorkingDirectory = NEW_PATH_TO_SQL_DATA_LOCATION\JOBS 
      			
      Note: This checklist does not cover servers using full-text search. If your server does use FT, then you will need to expand steps 6 and 7. Just move the FT directory in step 6 and search the registry for any references to it and change them as necessary.
    8. Start SQL Server.
    9. Finished!
  5. If you are using SQL Agent on your server do not forget to check that it is running.

Conclusion

So there we are, all system databases moved. Again, please note that this was mainly meant as a checklist for myself, but feel free to use it as a base for your own checklist when you need to move system databases. I urge you to read through it several times so you are sure what it says and what you are doing. Most of the steps here come from the Microsoft article Moving SQL Server databases to a new location with Detach/Attach, which will probably help you more than this list. I simply compiled them into an easy-to-follow, step-by-step list that I could use to cut the down-time as much as possible.

Total article views: 20645 | Views in the last 30 days: 19
 
Related Articles
BLOG

SQL Server – Changing Default Database Location for Server

When you create a new database in SQL Server without explicitly specifying database file locations, ...

FORUM

Database Default file Locations

Database Default Locations in Database Setting Under Server Properties

BLOG

SQL Server # Moving MASTER database in cluster environment

Few months back I have wrote post about moving MASTER and MSDB database to new location in stand alo...

ARTICLE

Database Master Key Error After Database Restore

When restoring a database to another server, you may get a master key error if using symmetric keys ...

FORUM

move Master database from the 2000 to 2005 SQL server

Move Master database from the 2000 to 2005 SQL server

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones