Moving system databases

When installing SQL Server 2000/2005/2008 many often forget where the system databases go on the filesystem. They are by default placed into C:\Program Files\Microsoft SQL Server \.... – Which we properly all can agree is not the optimal placement.

Note that to move the system databases require SQL server downtime, it doesn't take long though (depending on the size of course)

DISCLAIMER:

Please test this recipe on a test server before trying it in production – failing at any step will invalidate or totally stop you SQL server installation.

 

To move the system databases do as follows:

1. Shut down the SQL Server services – all of them
   
2. Start a command prompt (and keep it open)
   
3. Start the SQL server service in this nonstandard trace mode that skips recovery of the databases (except master)
   
4. NET START MSSQLSERVER /f /T3608 (or NET START MSSQLSERVER$Instancename /f /T3608 if you have a named instance)
   
5. Now modify the logical file placement by script:
   DO NOT MOVE THE FILES PHYSICALLY YET
   

Adapt the new file location to match YOUR filesystem

ALTER DATABASE MODEL MODIFY FILE (NAME = modellog , FILENAME = 'G:\SQLLogFiles\modellog.ldf')
GO
ALTER DATABASE MODEL MODIFY FILE (NAME = modeldev , FILENAME = 'H:\SQLDataFiles\model.mdf')
GO

ALTER DATABASE TEMPDB MODIFY FILE (NAME = templog , FILENAME = 'G:\SQLLogFiles\templog.ldf')
GO
ALTER DATABASE TEMPDB MODIFY FILE (NAME = tempdev , FILENAME = 'H:\SQLDataFiles\tempdb.mdf')
GO

ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBlog , FILENAME = 'G:\SQLLogFiles\MSDBlog.ldf')
GO
ALTER DATABASE MSDB MODIFY FILE (NAME = MSDBData, FILENAME = 'H:\SQLDataFiles\MSDBData.mdf')
GO

ALTER DATABASE master MODIFY FILE (NAME = master , FILENAME = 'H:\SQLDataFiles\master.mdf')
GO
ALTER DATABASE master MODIFY FILE (NAME = mastlog, FILENAME = 'G:\SQLLogFiles\Mastlog.ldf')
GO

As Per SQL Server 2008 the MSSQLSYSTEMRESOURCE database cannot be moved ! But in SQL Server 2005 the database location must be the same as the Master database.


For SQL 2005 use:

ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= 'H:\SQLDataFiles\mssqlsystemresource.mdf')
GO
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= 'H:\SQLDataFiles\mssqlsystemresource.ldf')
GO

The MSSQLSYSTEMRESOURCE database file and log file must remain on the same drive !

 

1. Now stop the MSSQLSERVER Service: NET STOP MSSQLSERVER (or NET STOP MSSQLSERVER$Instancename)
   
2. Move the files physically to the new location you have specified in the script
   
3. Change the startup parameters of the the SQL Server service in SQL Server Configuration Manager to match the new location of the master database. Eg:
   -dH:\SQLDataFiles\master.mdf
   -eH:\SQLDataFiles\log\ErrorLog
   -lG:\SQLLogFiles\mastlog.ldf
   
4. Start the MSSQLSERVER services normally
   

Please test this recipe on a test server before trying it in production – failing at any step will invalidate or totally stop you SQL server installation.