Relocating System Databases

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author: ChillyDBA

Date: 7 Jun 2020

Description

There is no generic code associated with this document, as every setup will be different.

Also, moving system databases should really be a last resort, and even then should only be done between servers at the same version/edition/SP level.

Migrations should really be done by transferring system settings by scripting/reapplying, thus ensuring that any nuances of updated system interfaces are used, and any potential incompatibilities are identified.

Notes

Moving databases files from one folder to another. Also includes transferring databases across servers This task can be split into two categories

• Moving user databases

• Moving system databases - master, msdb, tempdb, model

Of these moving the user databases is trivial compared to moving system databases. Background knowledge SQL Server databases are held in disk files. As a minimum they require one data file and one transaction log file. The data file defaults to extension .mdf (further files default to .ndf). The transaction log file defaults to extension .ldf. User databases are self contained and so can be moved easily. Tempdb is created on every restart using the model database as a template The file locations can be obtained from the table sysfiles in the database. A database cannot be detached if users are connected. Caution Before detaching a database make sure you have a backup - preferably test restore the backup before the detach.

System databases usually require sql server to be started with trace flag 3608. This omits the recovery of all databases except master. It means you cannot do much with the server but allows the location of system databases to be altered.

To start the SQL Server with trace flag 3608

• In enterprise manager right click on the server and select properties, click on startup parameters.

• Add -T3608 to the list of parameters and save.

• Restart SQL Server

Master

The location of the master database is defined by startup parameters.

• In enterprise manager right click on the server and select properties, click on startup parameters. Here you will see the entries for the master data and log files (and errorlog).

• Change these and close down the SQL Server.

• Move the files to the location specified.

• Restart the SQL Server.

MSDB

• Ensure that the SQLServer Agent is not set to start automatically with SQL Server

• Restart SQL Server with trace flag 3608

• Move the MSDB database by detach and attach as with a user database.

• Restart SQL Server without trace flag 3608.

TempDB

TempDB is moved via an alter database command

• In Master alter database tempdb modify file (name = tempdev, filename = 'fileloc')

  • alter database tempdb modify file (name = templog, filename = 'fileloc')

• Restart sql server to create the files in the new location.

Model

• Restart SQL Server with trace flag 3608

• Move the Model database by detach and attach as with a user database.

• Restart SQL Server without trace flag 3608.