Moving SQL Server System Databases
2. Start the SQL Server Services. On the command prompt, type the following command
NET START MSSQLSERVER
Once done, check whether the tempdb data and log files are present at the newly allocated location:
Execute the below T-SQL query against the tempdb database to double check whether the operation has been successfully performed.
select * from sysfiles
From the above screen capture, we can conclude that we have successfully moved the tempdb to the newly allocated location successfully.
Moving the master database
At present, the master database is present under the following path D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data and I want to move its mdf and ldf file to the paths named E:\Microsoft SQL Server\MSQL\Data and E:\Microsoft SQL Server\MSQL\Log respectively.
- Go to Start >All Programs > Microsoft SQL Server, > Configuration Tools > SQL Server Configuration Manager.
- In the SQL Server Services node, right-click the instance of SQL Server, in our case the name of the SQL Server instance is MSSQLSERVER and choose Properties:
- Click on the Advanced Tab as shown in the screen capture below:
- Select the text in the Startup parameters and copy it into a text file:
- As shown in the above screen capture, at present the data
and log file of the master database resides under the following path:
D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data
And we want it to reside under the following new paths:
Data File:E:\Microsoft SQL Server\MSQL\Data
Log File:E:\Microsoft SQL Server\MSQL\Log
- In the startup parameters, change the location of the data and log file as per the new path as shown in the screen capture below:
- Select Apply and then Select OK.
- Stop the SQL Server instance by Right Clicking on it and Selecting Stop.
- Move the data and log file of the master database to the new location, please refer the screen capture below:
- Restart the SQL Server instance by right clicking on it and Select Start, please refer the screen capture below.
- Verify that the changes have taken place successfully by
executing the below query against the master database:
select * from sysaltfiles where dbid=1
- From the above screen capture, we can conclude that the data and log file of the master database have moved properly to the respective new location.
In the next article I will discuss how to move the model, msdb and distribution databases. Please let me know if you have any comments or suggestions.
Pages: 1 2