A common task for any DBA building new SQL servers is to relocate where SQL Server stores user and system databases. This post will cover how to move system databases to different drives. I have already covered how to move user databases to other drives here. I also will not be addressing the tempDb databases as they are covered here.
Moving system databases is a bit different from moving user databases. This is especially true of the master database as it will require we change the startup parameters during this process.
We will be using this link as a reference.
Moving the master database
Here is a good reference blog post: How to move the master database
First, I will provide you with a query that will show us the current path for each system database. Keep this handy as you will rerun it many times during the move:
|
1 2 3 4 5 6 7 8 9 |
-- Verify the current settings SELECT DB_NAME(database_id) AS 'Database Name', name AS 'Logical File Name', physical_name AS 'Physical File Location', state_desc AS 'State' FROM sys.master_files WHERE database_id IN (DB_ID(N'msdb'), DB_ID(N'model'), DB_ID(N'master')) ORDER BY DB_NAME(database_id); GO |
We will start with moving the master database. The first task it to modify the startup parameters. The follow are the settings we need to change:
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
 |
3. In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.
 |
4. In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change.
5. In the Specify a startup parameter box, change the parameter to the new path of the master database.
6. In the Existing parameters box, select the –l parameter to move the master log file. Click Update to save the change.
7. In the Specify a startup parameter box, change the parameter to the new path of the master database.
The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.
|
1 2 |
-dC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\master.mdf -lC:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\mastlog.ldf |
If the planned relocation for the master data file is E:\SQLData, the parameter values would be changed as follows:
|
1 2 |
-dE:\SQLData\master.mdf -lE:\SQLData\mastlog.ldf |
NOTE: I also add the trace flags I usually set for each server here:
|
1 2 3 4 |
-T1117 -T1224 -T2371 -T4199 |
NOTE: Regarding trace flag 1118 from Paul Randal’s blog: [Edit 2016: This trace flag is required in all version of SQL Server up to and including SQL Server 2014. Every instance of SQL Server in the world should have this trace flag enabled. In SQL Server 2016, the behavior enabled by the trace flag is the default, so the trace flag is no longer required and has no effect.]
8. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
9. Move the master.mdf and mastlog.ldf files to the new location.
10. Restart the instance of SQL Server.
11. Verify the file change for the master database by running the following query.
The “Startup Parameters” final screen should look like this:
 |
Moving the resource and model databases
Moving the resource and model databases is fairly straight-forward. Simply perform the steps below:
1. Set the T-SQL script below to contain the correct new drive paths.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE master GO -- model database move ALTER DATABASE [model] MODIFY FILE (NAME = 'modeldev', FILENAME = 'S:\SqlData\model.mdf'); GO ALTER DATABASE [model] MODIFY FILE (NAME = 'modellog', FILENAME = 'S:\SqlData\modellog.ldf'); GO -- MSDB database move ALTER DATABASE [msdb] MODIFY FILE (NAME = 'MSDBData', FILENAME = 'S:\SqlData\MSDBData.mdf'); GO ALTER DATABASE [msdb] MODIFY FILE (NAME = 'MSDBLog', FILENAME = 'S:\SqlData\MSDBLog.ldf'); GO |
2. Run the script on the target server.
3. Stop the SQL service.
4, Move the files to the new location.
5. Restart the SQL service
6. Rerun the “verify settings” query to make sure everything is set correctly. If you do not have the correct path for the ALTER DTATBASE statement, the service will likely not start and throw an error.
Follow-up: After Moving All System Databases
If you have moved all of the system databases to a new drive or volume or to another server with a different drive letter, make the following updates.
Change the SQL Server Agent Error log path
If you do not update this path, SQL Server Agent will fail to start.
1. From SQL Server Management Studio, in Object Explorer, expand SQL Server Agent.
2. Right-click Error Logs and click Configure.
3. In the Configure SQL Server Agent Error Logs dialog box, specify the new location of the SQLAGENT.OUT file. The default location is C:\Program Files\Microsoft SQL Server\MSSQL12.\MSSQL\Log\.
 |
Change the SQL Server Agent log path
1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server Agent (for example, SQL Server (MSSQLSERVER)) and choose Properties.
3. Clicked on the Advanced tab.
 |
4. Change the value in the “Dump Directory” path to the new location.
5. Restart the SQL Agent services.
Change the database default location
Creating a new database may fail if the drive letter and path specified as the default location do not exist.
1. From SQL Server Management Studio, in Object Explorer, right-click the SQL Server server and click Properties.
2. In the Server Properties dialog box, select Database Settings.
3. Under Database Default Locations, browse to the new location for both the data and log files.
 |
4. Change the paths to the new locations.
5. Stop and start the SQL Server service to complete the change.
Views – 3709