Move SQL Server System and User Databases to Another Location Step by Step Process
In this SQL Server tutorial, you will learn how to move all system and user databases (Master, Model, MSDB, TempDB, and user databases) to a new drive safely step by step. This guide is ideal for DBAs, beginners, and IT professionals looking to optimize SQL Server performance, manage disk space, or migrate databases to a new location.
First, check the current file locations using this command:
SELECT name, physical_name FROM sys.master_files;
For the Master database, modify the startup parameters in SQL Server Configuration Manager with the new MDF and LDF paths, then stop the SQL Server service, move the files manually to the new folder, and restart the service. If needed, run the below commands before restarting:
ALTER DATABASE master MODIFY FILE (NAME='master', FILENAME='E:\SQLData\master.mdf');
ALTER DATABASE master MODIFY FILE (NAME='mastlog', FILENAME='E:\SQLLogs\mastlog.ldf');
For the Model and MSDB databases, use these commands to set new paths:
ALTER DATABASE model MODIFY FILE (NAME='modeldev', FILENAME='E:\SQLData\model.mdf');
ALTER DATABASE model MODIFY FILE (NAME='modellog', FILENAME='E:\SQLLogs\modellog.ldf');
ALTER DATABASE msdb MODIFY FILE (NAME='MSDBData', FILENAME='E:\SQLData\MSDBData.mdf');
ALTER DATABASE msdb MODIFY FILE (NAME='MSDBLog', FILENAME='E:\SQLLogs\MSDBLog.ldf');
Then stop the service, move the files, and start it again.
For the TempDB database, use these commands and restart SQL Server to recreate the files in the new location:
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='E:\SQLData\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME='templog', FILENAME='E:\SQLLogs\templog.ldf');
For user databases like TestDB, follow the same steps. Run these commands:
ALTER DATABASE TestDB MODIFY FILE (NAME='TestDB_Data', FILENAME='E:\SQLData\TestDB_Data.mdf');
ALTER DATABASE TestDB MODIFY FILE (NAME='TestDB_Log', FILENAME='E:\SQLLogs\TestDB_Log.ldf');
ALTER DATABASE TestDB SET OFFLINE;
(move files manually to the new folder)
ALTER DATABASE TestDB SET ONLINE;
Finally, test the database connection, verify the new file paths, and ensure all databases are working correctly.
Keywords: move SQL Server databases, relocate MDF LDF files, move system databases, change database file path, SQL DBA tutorial, SQL Server migration, TempDB move, master model msdb relocation, SQL performance tuning, SQL Server file move, SQL Server DBA tasks.