isitdevops.com/databases

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

Get the old file locations first:

SELECT name AS LogicalName, physical_name AS PhysicalPath, CASE type_desc

WHEN ‘LOG’ THEN ‘Log’

WHEN ‘ROWS’ THEN ‘Data’ ELSE type_desc END AS FileType,

state_desc AS Status

FROM sys.master_files

WHERE database_id

–WHERE database_id >= 5 — User DBs

–WHERE database_id = DB_ID(‘model’) — Specific DB

 

For 2008 on there is no longer a requirement to use the 3608 trace flag, or startup the instance in single-user mode.

ALTER DATABASE MODIFY FILE commands can be used for the model and msdb moves, just like tempdb.

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

 

Model, msdb, and tempdb

Issue the following ALTER DATABASE T-SQL commands, updating the new file paths as appropriate:

— e.g. Move model

USE master

GO

ALTER DATABASE model MODIFY FILE (NAME = modeldev,FILENAME = ‘C:\SQLData\model.mdf’);

GO

ALTER DATABASE model MODIFY FILE (NAME = Modellog,FILENAME = ‘C:\SQLLogs\modelLog.ldf’);

GO

etc

then

— Move msdb

— Move tempdb

 

Stop SQL Server (from a Command Prompt):

net stop SQLSERVERAGENT

net stop MSSQLSERVER

Move the model.mdf and MSDBData.mdf files from D:\SQLData\ to C:\SQLData\ (do not move tempdb.mdf).

Move the modelLog.ldf and MSDBLog.ldf files from D:\SQLLogs\ to C:\SQLLogs\ (do not move tempLog.ldf).

Delete the tempdb.mdf and tempLog.ldf files from D:\SQLData\ and D:\SQLLogs\; These will be recreated at startup of the SQL instance so do not need to be moved.

Start SQL Server (from a Command Prompt) leaving the SQL Server Agent for now:

net start MSSQLSERVER

 

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

Master

There is no longer a requirement to place the SQL instance in master recovery mode first.

Start “SQL Server Configuration Manager”, click on the “SQL Server Services” option, right-click on the SQL Server instance and click on “Properties”. Click on the “Advanced” tab.

Edit the “Startup Parameters” by changing the existing master MDF and LDF entries:

-dC:\SQLData\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\LOG\ERRORLOG;-lC:\SQLLogs\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;}

-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 file.

Apply the changes, then stop SQL Server (from a Command Prompt):

net stop MSSQLSERVER

Move the master.mdf files from D:\SQLData\ to C:\SQLData\ and the mastlog.ldf file from D:\SQLLogs\ to C:\SQLLogs \.

Restart SQL Server along with the SQL Server Agent:

net stop MSSQLSERVER

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;}

You can check the locations of the System database files after the move(s) by running:

SELECT name AS LogicalName, physical_name AS PhysicalPath,CASE type_desc

WHEN ‘LOG’ THEN ‘Log’

WHEN ‘ROWS’ THEN ‘Data’ ELSE type_desc END AS FileType,

state_desc AS Status

FROM sys.master_files WHERE database_id