2000 – Move system databases
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
Â
Â
Discussion ¬