SQL Server: Change System Default Database and Log file Location

Appendix: Change System Default Database and Log file Location

We can change all system and resource database to relocation as following steps:
1. Use Alter database to change database files and database log files to new location:
Execution following query:
use [master]
go
alter database [master] modify file (name = 'mastlog', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\TransactionLog\System\mastlog.ldf')
go
alter database [master] modify file (name = 'master', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\DATA\System\master.mdf')
go
alter database [model] modify file (name = 'modellog', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\TransactionLog\System\modellog.ldf')
go
alter database [model] modify file (name = 'modeldev', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\DATA\System\model.mdf')
go
alter database [msdb] modify file (name = 'MSDBLog', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\TransactionLog\System\MSDBlog.ldf')
go
alter database [msdb] modify file (name = 'MSDBData', filename = 'S:\SQLServer\MSSQL10_50.DIST_CUBE\MSSQL\DATA\System\MSDBData.mdf')
go
--alter database tempDB modify file (name = templog, filename = 'S:\SQLSERVER\TransactionLog\TempDB\templog.ldf')
--go
--alter database tempDB modify file (name = tempdev, filename = 'S:\SQLSERVER\Data\TempDB\tempdb.mdf')
--go

2. Copy all system database files and database log files to new location:
3. To active node, click start, all programs, MS SQL 2008, Configuration tools, SQL Server configuration manager:
4. In SQL server configuration manager, we need to modify all default database file location and log file location: Click SQL Server Services, right click SQL Server (Instance name) and select property, then go to Advanced tab:
• Change “Dump Directory” to S:\sqlserver\zystem\log\
• Change “Startup Parameters” to:
-dS:\SQLSERVER\Data\SystemDB\master.mdf;-eS:\SQLSERVER\Zystem\Log\ERRORLOG;-lS:\SQLSERVER\TransactionLog\SystemDB\mastlog.ldf

Click OK to leave this section
In the same SQL Server Configuration Manager, we also need to change SQL Agent’s log file location: Right click SQL Server Agent (Instance name), select properties.
• In Advanced tab, change “Dump Directory” to S:\SQLSERVER\Zystem\Log\


6. Click OK to leave this section. Do not copy or move SQL agent log file to the new location folder. After system reboot or failover, new configuration will apply to SQL server service.