There comes a time when you have to do something which appears impossible at start. One of the things I had to confront is moving my SQL database files from the D-drive to the L-drive.
Moving regular SQL database files isn’t that hard at all. Just detach the database, move the files to a new location and attach the database again. However, system databases can’t be moved in this way, which makes sense if you think about it. To move the system databases, you need to run some queries. This method is described on MSDN and there are also some forum posts which describe the necessary actions.
For future reference, I’ll describe the necessary steps below.
First, stop the SQL Server and add the following to the startup parameters:
This will make sure the master database files are searched at the new location (_L:\Databases\MSSQL.1\MSSQL\Data\System_)
Now move the physical master database files to the new location.
At this moment it’s possible to start SQL Server again by running the following command:
NET START MSSQLSERVER /f /T3608
This will start SQL Server in master-only mode. You can open Management Studio now again and start a new query window (the Object Explorer doesn’t work in this mode) (using SQLCmd wil also work). In this new query window you will have to execute this script to move the databases to the _L:\Databases\MSSQL.1\MSSQL\Data\System_ location.
ALTER DATABASE master MODIFY FILE (NAME = master, FILENAME = 'L:\Databases\MSSQL.1\MSSQL\Data\System\master.mdf') GO ALTER DATABASE master MODIFY FILE (NAME = mastlog, FILENAME = 'L:\Databases\MSSQL.1\MSSQL\Data\System\mastlog.ldf') GO ALTER DATABASE model MODIFY FILE (NAME = modeldev, FILENAME = 'L:\Databases\MSSQL.1\MSSQL\Data\System\model.mdf') GO ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = 'L:\Databases\MSSQL.1\MSSQL\Data\System\modellog.ldf') GO ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData, FILENAME = 'L:\Databases\MSSQL.1\MSSQL\Data\System\MSDBData.mdf') GO ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog, FILENAME = 'L:\Databases\MSSQL.1\MSSQL\Data\System\MSDBLog.ldf') GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'L:\Databases\MSSQL.1\MSSQL\Data\System\tempdb.mdf') GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'L:\Databases\MSSQL.1\MSSQL\Data\System\templog.ldf') GO ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME='L:\Databases\MSSQL.1\MSSQL\Data\System\mssqlsystemresource.mdf'); GO ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME='L:\Databases\MSSQL.1\MSSQL\Data\System\mssqlsystemresource.ldf'); GO
After this script has succeeded you can stop the SQL Server instance again.
Now move all the system database files to the new location, remove the startup parameters and check if all databases have successfully moved to the new location by using the following query.
SELECT name, physical_name, state_desc FROM sys.master_files
This should produce something similar like the screenshot below.
Keep in mind, you won’t see the mssqlsystemresource database in this query, because it’s a ‘hidden’ database used for internal operations of SQL Server when you want to upgrade your server.