• 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