Scripts for Moving System Databases

Start up parameters for SQL Server service

ORIGINAL

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

Changing location of master data and master log files

NEW

-dF:\Sql\Data2008R2\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lG:\Sql\Data2008R2\master.ldf

NOTE:  Use one tempdb file per logical cpu (core), up to 8.

       Put tempdb log file on same disk as user data files.

-- master

ALTER DATABASE master MODIFY FILE ( NAME = 'master', FILENAME = 'F:\Sql\Data2008R2\master.mdf')

ALTER DATABASE master MODIFY FILE ( NAME = 'mastlog', FILENAME = 'G:\Sql\Data2008R2\master.ldf')

ALTER DATABASE master MODIFY FILE ( NAME = 'master', SIZE = 50MB , FILEGROWTH = 50MB )

ALTER DATABASE master MODIFY FILE ( NAME = 'mastlog', SIZE = 25MB , FILEGROWTH = 25MB )

-- model

ALTER DATABASE model MODIFY FILE ( NAME = 'modeldev', FILENAME = 'F:\Sql\Data2008R2\model.mdf')

ALTER DATABASE model MODIFY FILE ( NAME = 'modellog', FILENAME = 'G:\Sql\Data2008R2\model.ldf')

ALTER DATABASE model MODIFY FILE ( NAME = 'modeldev', SIZE = 100MB , FILEGROWTH = 100MB )

ALTER DATABASE model MODIFY FILE ( NAME = 'modellog', SIZE = 100MB , FILEGROWTH = 100MB )

-- msdb

ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBData', FILENAME = 'F:\Sql\Data2008R2\msdb.mdf')

ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBLog', FILENAME = 'G:\Sql\Data2008R2\msdb.ldf')

ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBData', SIZE = 500MB , FILEGROWTH = 200MB )

ALTER DATABASE msdb MODIFY FILE ( NAME = 'MSDBLog', SIZE = 100MB , FILEGROWTH = 100MB )

-- tempdb (NOTE:  tempdb log goes on same disk as user data)

ALTER DATABASE tempdb MODIFY FILE ( NAME = 'tempDev', SIZE = 2GB , FILEGROWTH = 1GB )

ALTER DATABASE tempdb MODIFY FILE ( NAME = 'tempLog', SIZE = 2GB , FILEGROWTH = 1GB,

      FILENAME = 'F:\Sql\Data2008R2\tempdb.ldf')

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev1', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'E:\Sql\Data2008R2\tempdb1.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev2', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'E:\Sql\Data2008R2\tempdb2.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev3', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'E:\Sql\Data2008R2\tempdb3.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev4', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'E:\Sql\Data2008R2\tempdb4.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev5', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'E:\Sql\Data2008R2\tempdb5.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev6', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'E:\Sql\Data2008R2\tempdb6.ndf' )

ALTER DATABASE tempdb ADD FILE ( NAME = 'tempDev7', SIZE = 2GB , FILEGROWTH = 1GB, FILENAME = 'E:\Sql\Data2008R2\tempdb7.ndf' )

After running all the above, then change the location of the master files (rename if necessary) and then stop/start the SQL Server service.