Move the Tempdb and Master databases on SQL Server

Due to various reasons, moving the database and recording files to another hard drive to increase performance is essential. When you want to move a typical user database to another drive to increase performance or to split logs, you can run sp_detach and sp_attach to perform the migration. However, for moving the Master and Tempdb databases, you must apply other rules. In this article, let's take a look at the steps of moving these databases.

Move the Master database

Have you ever had to move the master database file to another drive? If you have never performed this function, follow these guidelines to successfully migrate the master database. First, right-click on SQL Server on Enterprise Manager (EM) and select Properties . Then click Startup Parameters as shown in Figure A, we will see the following parameters:

1. -d is the full path of the master database data file.
2. -e is the full path of the error log file.
3. -l is the full path of the master database record

Picture A

Now if you want to move the files, you will have to remove the current entry and recreate a new entry with the correct path. For example, you will move the Master database table to (C: Test). You will delete -l [path] by selecting the old parameter and selecting remove (Figure B). The next step is to add the following entry (Figure C), -l (c: Testmastlog.ldf) and click OK twice. Then stop SQL Server and move the mastlog.ldf to the new location.

Figure B

Note : Make sure the mastlog.ldf is moved to the location you configured at the boot parameters. If you move the file to an unknown location on the boot parameters, SQL Server will not be activated.

Figure C

If you successfully move the Master database, when you start SQL Server, you will see the following screen:

Figure D

Move Tempdb

To move the tempdb database, open the query analysis tool and run the following query:

use master go

Alter database tempdb modify file (name = tempdev, filename = 'E: Sqldata empdb.mdf')

go

Alter database tempdb modify file (name = templog, filename = 'E: Sqldata emplog.ldf')

Go

Depends on where you move Tempdb which specifies the file name = parameter. You will move the templog to (c: est) as shown in Figure E. After running the query, delete the old file immediately after starting SQL Server.

Figure E

Move MSDB database

To move the MSDB and Model databases, follow these steps. First, right-click on the name of SQL-Server and click Properties . From the General tab, select the boot parameters. Next, enter the parameter -T3608 . Click OK , stop and restart SQL Server. After restarting, remove the database and move them to the appropriate locations.

Hard disk space is getting more crowded and you need to transfer system database files to another drive, following the above procedures will help you transfer data efficiently and reliably.