Move Database File paths of System
Database
There are 4 System Databases – Master, Model, msdb and tempdb. To move the database path of model, msdb and tempdb, follow the below steps. But, prior to moving the msdb database, check whether service broker is enabled. This is required if database
mail is enabled on the SQL Server. To check whether service broker is
enabled, Open SQL Server Management Console, and click New Query. New Query Window will be opened.
Enter the below SQL Query.
SELECT is_broker_enabled
FROM
sys.databases
WHERE name = N’msdb’;
If the output is 1,
it means that the service broker is enabled.
To move the
database file paths of the above said 3 system databases, execute the following
Queries. The below queries assume that the new database file path is D:\SQLData.
Alter DATABASE model MODIFY FILE (NAME
= modeldev , FILENAME = ‘D:\SQLData\model.mdf’ )
Go
Alter DATABASE model
MODIFY FILE (NAME = modellog , FILENAME = ‘D:\SQLData\modellog.ldf’
)
Go
Alter DATABASE msdb MODIFY FILE (NAME = MSDBData ,
FILENAME = ‘D:\SQLData\MSDBData.mdf’ )
Go
Alter DATABASE msdb MODIFY FILE
(NAME = MSDBData , FILENAME = ‘D:\SQLData\MSDBlog.ldf’ )
Go
ALTER DATABASE
tempdb MODIFY FILE (NAME = tempdev, FILENAME =
‘D:\SQLData\tempdb.mdf’);
GO
ALTER DATABASE tempdb MODIFY FILE (NAME =
templog, FILENAME = ‘D:\SQLLog\templog.ldf’);
GO
After executing the above steps, a warning message will be received, indicating that
the change will take place only after the next restart of the SQL Server. Stop
the SQL Server from the SQL Server Configuration Manager which can be opened
from Start –> All Programs –> SQL Server –> SQL Server Configuration
Manager.
Copy the msdb.mdf, msdblog.ldf, model.mdf,
modellog.ldf, temp.mdf, templog.ldf file to the new
location
Start the SQL Server from the SQL Server
Configuration Manager (or from the Services console from Run -> ‘services.msc’)
Open SQL Server Management Console and verify the file path as mentioned above
To move the database file path of master database, follow the below steps. Master Database should be dealt with with utmost care, because the SQL Server instance will not start if something happens to this database. Master Database contains information of all the databases, including user Databases.
1. Open SQL Server Configuration Manager
2. Right Click the properties of the SQL Server Instance
3. Select Advanced tab
4. Modify the value of ‘Start Parameter’ to
‘-dD:\SQLData\master.mdf;-lD:\SQLData\mastlog.ldf’
5. Stop the SQL Server service from SQL Server Configuration Manager
6. Start the SQL Server Instance in master-only recovery mode. To do this follow the steps below
Open Command Prompt and enter the command: NET START MSSQL$GLOBAL /f /T3608
(The parameters are case sensitive)
7. Change the location of the resource database. The resource database should be in the same folder as that of the master database. To move the resource database, execute the below queries.
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= ‘D:\SQLData\mssqlsystemresource.mdf’);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= ‘D:\SQLData\mssqlsystemresource.ldf’);
GO
8. Clear the query window and execute the following command to set the resource database as read-only
ALTER DATABASE mssqlsystemresource SET
READ_ONLY;
9. Restart the SQL Server from SQL Server service from SQL Server Configuration Manager