How to move SQL System Databases

One day or another comes the day to move the location or System Databases in your work as a DBA. Could because you need to fix the initial installation or because you are doing a storage layout reorganization.

System Databases are four:

  • Master
  • Model
  • MSDB
  • Temp DB

Since, this is a delicated operation we should move them one by one, and I strongly recommend the following order: TempDB, MSDB, Model and last Master.

In the following steps E:/DATA is the destination path, you will have to change it to your new system databases location.

  1. Move Temp DB database:

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = ‘E:DATAtempdb.mdf’);

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = ‘E:DATAtempLog.ldf’);

GO

Stop the instance, move the mdf and ldf files to the new location and start the instance.

  1. Move MSDB Database

USE master;

GO

ALTER DATABASE msdb

MODIFY FILE (NAME = MSDBData,FILENAME = ‘E:DATAMSDBData.mdf’);

GO

ALTER DATABASE msdb

MODIFY FILE (NAME = MSDBLog, FILENAME = ‘E:DATAMSDBLog.ldf’);

GO

Stop the instance and SQL Server Agent, move the mdf and ldf files and start the instance. Since MSDB is related to SQL server agent jobs, try to view the history jobs or run a job to verify the MSDB is working properly.

  1. Move Model Database

USE master;

GO

ALTER DATABASE model

MODIFY FILE (NAME = modeldev,FILENAME = ‘E:DATAmodel.mdf’);

GO

ALTER DATABASE model

MODIFY FILE (NAME = Modellog,FILENAME = ‘E:DATAmodelLog.ldf’);

GO

Stop the instance and SQL Server Agent, move the mdf and ldf files and start the instance. Since the model database is used for creating new databases a good test is to create a new one.

  1. Move MASTER Database

Open SQL Server Configuration Manager, locate the SQL Server Service, double-click and edit the Advanced Properties tab:

Change the startup parameters to the new location.

  • “-d” flag refers to mdf data destination
  • “-l” flag refers to ldf log destination
  • “-e” flag refers to errorlog folder destination

s1

PD: After moving tempDB, MSDB and/or Model I came across this error when trying to modify a job step:

s2

After restarting the server, it works normally. I need to confirm that this is normal

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s