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.
/* 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,
Open the Advanced tab
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.
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’);
ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\modellog.ldf’);
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf’);
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBLog.ldf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf’);
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = ‘D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf’);
/* 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*/