SQL Server moving system databases

I’ve recently had cause to move all of the system databases on a SQL Server 2008R2 instance to another drive and this script is a fairly basic instruction on how to do it.
Here is the BOL page that explains this.
Thanks to Greg Robidoux for his simplification of that BOL page.
USE [master]

/* 1 – Moving master
master db is special and needs to be used to move all the others.
It’s own location is controlled by startup params for the service

Open SQL Server Configuration manager
Right click the appropriate instance of SQL Server,
Click Properties,
Open the Advanced tab
Startup parameters,
The entry that starts with -d should be changed to the new database file location
The entry that starts with -l should be changed to the new log file location
*/

/*2 – change the path for the system databases.
general Format:

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = ‘new_path\os_file_name’ )

The database names are visible in the object explorer in SQL Server Management Studio (SSMS)
the logical_name and the os_file_name can be found in SSMS by right clicking on the database in question and look at properties –> files

In this example I am moving the files to the D drive but you could move them to any drive or directory you like.
*/

ALTER DATABASE model MODIFY FILE (NAME = modeldev, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf’);
GO
ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf’);
GO

ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf’);
GO
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf’);
GO

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf’);
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf’);
GO

/* 3 – Stop SQL Server instance service */
/* 4 – Move the MDF and LDF files to the new path for the master, model and msdb databases*/
/* 5 – Delete the old tempdb files (new tempdb file will be recreated when SQL Server was started, files don’t need to be moved*/
/* 6 – Start SQL Server instance*/

Leave a Reply

Your email address will not be published. Required fields are marked *