Steps to move system database files with scripts

TEMPDB

1.  ------get file details

use tempdb

go

select * from sys.database_files

2.  -----run following (MODIFY FILE NAMES AND PATH AS NEEDED)

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'S:\MSSQL\TempDB\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'S:\MSSQL\TempDB\templog.ldf');

GO

3.  -----stop SQL

4.  -----start SQL

5.  -----run following to vefiry

use tempdb

go

select * from sys.database_files

MSDB

1.  ------get file details

use msdb

go

select * from sys.database_files

2.  ------move files

ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBData , FILENAME = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf')

GO

ALTER DATABASE MSDB MODIFY FILE ( NAME = MSDBLog , FILENAME = 'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf')

GO

3.  -----stop services

4.  -----copy files

5.  -----rename old files

6.  -----start services

7.  -----verify

use msdb

go

select * from sys.database_files

8.  -----if SQL Agent does not start check if service broker is enabled

SELECT is_broker_enabled

FROM sys.databases

WHERE name = N'msdb';

--USE master

--ALTER DATABASE ServiceBrokerTest

--SET ENABLE_BROKER;

MODEL

-----use same procedure as msdb

MASTER

1.  Stop services

2.  modify start parameters to new path

3.  copy files

4.       start services