About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Friday, April 3, 2015

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.


No comments:

Post a Comment