Normal 0 false false false EN-GB JA X-NONE

Master

Update the -d and -l startup parameters for your SQL Server instance with the new location for your master MDF and LDF files (do not move any files at this stage). To do this, open SQL Server Enterprise Manager, right-click the server name and click Properties.

 

Normal 0 false false false EN-GB JA X-NONE

Open up the Startup Parameters.

Remove the existing master MDF and LDF entries:

-dD:\MSSQL\MSSQL\Data\master.mdf

-lD:\MSSQL\MSSQL\Data\mastlog.ldf

 

Normal 0 false false false EN-GB JA X-NONE

Add the new file locations:

-dC:\MSSQL\Data\master.mdf

-lC:\MSSQL\Data\mastlog.ldf

 

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

Stop SQL Server (from a Command Prompt):

net stop SQLSERVERAGENT

net stop MSSQLSERVER

Move the master.mdf and mastlog.ldf files from D:\MSSQL\MSSQL\Data to the new location at C:\MSSQL\Data.

Start SQL Server (from a Command Prompt) leave the SQL Server Agent down.

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

net start MSSQLSERVER

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

TempDB

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

USE tempdb

GO

EXEC sp_helpfile

GO

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

Then issue ALTER DATABASE SQL commands to change the file location of the tempdb database files:

USE master

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = ‘tempdev’, FILENAME = ‘C:\MSSQL\Data\tempdb.mdf’);

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = ‘templog’, FILENAME = ‘C:\MSSQL\Data\templog.ldf’);

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = ‘templog2’, FILENAME = ‘C:\MSSQL\Data\templog2.ldf’);

GO

Restart SQL Server:

net stop MSSQLSERVER

net start MSSQLSERVER

Now delete the old tempdb files (they are recreated in the new location at startup).

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

Model and msdb

The model databases should be moved first, followed by the msdb database.

The order in which these are moved is very important and could prevent the instance from starting if done incorrectly.

Add the -T3608 startup parameter to the instance:

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;} Open up the Startup Parameter.

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

Add the -T3608 startup parameter to the instance.

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

Navigate to the msdb database properties, then select the Options tab.

Then restrict access to the msdb database by placing it into single-user mode first.

Repeat for the MODEL database.

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;} Both the model and msdb databases should have ‘(Single User)’ appended to them when viewed in SQL Server Enterprise Manager,

 

 

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

Restart SQL Server:

 

net stop MSSQLSERVER

net start MSSQLSERVER

You will not be able to access any user databases at this time, and you should not perform any operations other than the steps below while this trace flag is set.

Open SQL Query Analyzer to detach the model, then msdb databases the using the following commands:

USE master

GO

sp_detach_db ‘model’

GO

sp_detach_db ‘msdb’

GO

Move the MDF and LDF files for the msdb and model databases to the new location.

Attach the model and msdb databases (in that order*) using its new location file locations:

USE master

GO

sp_attach_db ‘model’,’C:\MSSQL\Data\model.mdf’,’D:\MSSQL\Data\modellog.ldf’

GO

sp_attach_db ‘msdb’,’C:\MSSQL\Data\msdbdata.mdf’,’D:\MSSQL\Data\msdblog.ldf’

GO

 

 

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

The order of reattachment is important here – you must deal with model first and then msdb.

Go back to SQL Server Enterprise Manager and remove the -T3608 startup parameter.

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

Finally, restart SQL Server and bring up the SQL Server Agent:

net stop MSSQLSERVER

net start MSSQLSERVER

net start SQLSERVERAGENT

 

You can also check the files locations for a particular System database at any time by running:

USE

GO

EXEC sp_helpfile

GO

 

Normal 0 false false false EN-GB JA X-NONE /* Style Definitions */ table.MsoNormalTable {mso-style-name:”Table Normal”; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:””; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:”Cambria”,serif; mso-fareast-language:EN-US;}

References:

https://support2.microsoft.com/default.aspx?scid=kb;en-us;224071