Moving system databases

Three different approaches depending on the database being moved. Whilst this not required, it’s a good idea to run tempdb and master file moves as independent changes because these can result in SQL failing to start back up again! So if you do have a problem it’s easier to determine which one is stopping SQL from restarting.

General steps to move a data file

Update data file location
ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )
  • database_name is the name of the database
  • logical_name is the logical name of the file you are going to move
  • new_path is the full path to the new location
  • os_file_name is the name of the file including extension
Check update is correct
SELECT name, physical_name, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'database_name');

Replace database_name with the name of the database from the previous query.

Process for moving a data file other than master or tempdb

  1. Run alter statement for each data file & log file.
  2. Check you are happy with updates.
  3. Shut down SQL Server.
  4. Move files.
  5. Restart SQL Server.

Tempdb

Since tempdb is re-created at each restart, you do not need to move the files, simply set the new location and restart SQL Server;

  1. Run alter statement for each data file & log file.
  2. Check you are happy with updates.
  3. Restart SQL.
  4. Check that the files have moved as required.
  5. If happy, delete the files at old location.

Master

The master database is configured within SQL Server Configuration Manager within the properties of the SQL Server service of the appropriate instance;

In more recent versions of SQL the startup properties has its own tab and the information is laid out better. In earlier versions, the properties are held on a single line separated by semi colons. Switch -d is the location of the data file, switch -l is the location of the log file. Selecting one of the entries presents the file path as shown below;

  1. Shut down SQL Server using configuration manager.
  2. Update the location of the data file or log file (or both) to the new location.
  3. Locate the files and move as required.
  4. Restart SQL.

*Note, if something goes wrong with moving the master database, SQL will not start! If you hit this issue, you can determine what went wrong by locating the log file. You can find this from the final startup parameter (switch -e). This log file can be opened with a text editor, this will explain what went wrong with your database move!

Share this post...

Leave a Reply

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