• Madeira Team

Moving System Databases in SQL Server 2005/2008

This article may be useful when you want to relocate your system databases to another disk on the same server (same instance).  There could be a few reasons to do this: Not enough space on the current disk for system databases, disk maintenance or hardware failure.

We will move system databases from c:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLData to a new location where mdf files will be at C:System DatabasesData  and ldf files will be at  C:System DatabasesLog.  The new location can be another disk, but I have only one disk on my computer.

Moving tempdb database

First of all, we will check where the files are located now:

Transact-SQL

USE tempdb GO sp_helpfile GO

1

2

3

4

USE tempdb

GO

sp_helpfile

GO

The result is:

moving-databases-1-1024x90

We should remember this path in order to delete old files from there after movement.

We know that tempdb is deleted and recreated every time we restart the SQL Server service, so we only have to change file configuration in the database and check the configuration again. In this example we have only two files in tempdb.  If you have more files in your tempdb, move them all:

Transact-SQL

USE master; GO ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = N'C:System DatabasesDatatempdb.mdf' ); GO ALTER DATABASE tempdb MODIFY FILE ( NAME = templog, FILENAME = N'C:System DatabasesLogtemplog.ldf' ); GO USE tempdb GO sp_helpfile

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE

(

NAME = tempdev,

FILENAME = N'C:System DatabasesDatatempdb.mdf'

);

GO

ALTER DATABASE tempdb

MODIFY FILE

(

NAME = templog,

FILENAME = N'C:System DatabasesLogtemplog.ldf'

);

GO

USE tempdb

GO

sp_helpfile

moving-databases-2

Transact-SQL

'The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.'

1

2

3

4

'The file "tempdev" has been modified in the system catalog. The

new path will be used the next time the database is started.

The file "templog" has been modified in the system catalog. The

new path will be used the next time the database is started.'

Now we can see that the location has changed and that the message indicates that the new path will be used after restarting the service. We will not restart the service for now; we’ll restart the services after moving the other databases.

Moving model and msdb databases

Before moving system databases we should back it up.

There are two ways to move a model and msdb databases to another disk or another location.

First way is like the way we moved tempdb with changing the database configurations. Before changing the configuration we must check where database files are, so that after changing the configuration and stopping SQL Server service, we will know the path to the database files, in order to move them to the new location.

Transact-SQL

USE model GO sp_helpfile GO USE msdb GO sp_helpfile GO

1

2

3

4

5

6

7

8

USE model

GO

sp_helpfile

GO

USE msdb

GO

sp_helpfile

GO

The results are: