Moving system databases in SQL Server 2008, 2005
To move all the system and user databases from “C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA” folder to “C:System databases” location.
We can use below query to check current location of system databases:
Select name, physical_name from sys.master_files
1.Moving Tempdb database:
We need to use the Alter database command to move tempdb database files from one location to another as shown in the below screenshot.
Use master
go
Alter database tempdb modify file (name=tempdev,filename='C:System databasestempdb.mdf')
go
Alter database tempdb modify file (name=templog,filename='C:System databasestempdb.ldf')
go
Once we execute the above command the o/p will be something like,
The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “templog” has been modified in the system catalog. The new path will be used the next time the database is started.
The next time the Sql Service is restarted the tempdb database files will be created in the new location.
The old files will still exist which will need to be deleted manually.
Moving master database:
First stop the SQL Server Services. Then copy(copy not Cut) the Files to new location.
Then we need to change the path to point to the new location where master database files will be located in the SQL Server startup parameters (in SQL Server configuration manager).
Right click on SQL Server and go to properties – advanced tab.
Original path in Startup parameters will be similar to the one shown below,
-d C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAmaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLLogERRORLOG;-lC:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA mastlog.ldf
New path in Startup parameters which we have to enter will be similar to the one shown below since I am going to change the location of the master database to C:System databases folder the command will be like something,
-dC:System databasesmaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL10_50.VBDEVMSSQLLogERRORLOG;-lC:System databasesmastlog.ldf
If you wish to change the location of the error log to a new location please specify that as well next to –e in the above command.
-d implies that it’s the location data file of master database.
-l implies that it’s the location log file of master database.
-e implies that it’s the location the SQL Server error log file.
After changing the new path in startup parameter, SQL Server will prompt that a SQL Service must be restarted so that the changes will take effect.
After Successful starting up the Services and verifying the locations changes inside below query
Select name, physical_name from sys.master_files
Delete the old files in old location after verifying.
Moving Msdb and Model database:
Now For MSDB and Model, We need to use Alter database to move the database to new location similar to the movement of tempdb database.
After running Alter Command below, need to stop Sql Service and copy the database files to new location and then start Sql service.
The same procedure is applicable for both Msdb and Model databases.
For msdb use the below command:
Use master
go
Alter database msdb modify file (name=msdbdata,filename='C:System databasesmsdbdata.mdf')
go
Alter database msdb modify file (name=msdblog,filename='C:System databasesmsdblog.ldf')
go
For model db use the command below:
Use master
go
Alter database model modify file (name=modeldev,filename='C:System databasesmodel.mdf')
go
Alter database model modify file (name=modellog,filename='C:System databasesmodellog.ldf')
go
Finally we can check the path of all the sytem databases by using below query:
Select name, physical_name from sys.master_files
