Moving System Databases - A Checklist

By Christoffer Hedgate, 2004/11/30

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!

Make sure you have backups of all user databases plus master, model and msdb.
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!
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.
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!
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: 20539 | Views in the last 30 days: 24

BLOG

Moving System Databases - A Checklist