IT Tricks

Migrate the Tempdb and Master databases on SQL Server


Due to various reasons, it is essential to move the database and write files to another hard drive to increase performance. When you want to move a typical user database to another drive for increased performance or to separate records, you can run it. sp_detach and sp_attach to make the move. However, for migrating the Master and Tempdb database, different rules apply. In this article, let’s explore the steps of migrating these databases.

Database Master Migration

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

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


Picture A

Now if you want to move files, you will have to remove the existing item and create a new entry with the correct path. For example, you would move the Master database record to (C: Test). You will delete -l [path] by selecting the old parameter and choosing remove (Figure B). In the next step you will add the following item (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

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


Figure C

If the Master database is successfully moved, when starting 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 the Tempdb that 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 as soon as after starting SQL Server.


Figure E

MSDB database migration

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

The hard drive space becomes more and 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.

Leave a Reply

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