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:

moving-databases-3

Now we can change the path on the database files and check that it has changed:

Transact-SQL

USE master; GO ALTER DATABASE model MODIFY FILE ( NAME = modeldev, FILENAME = N'C:System DatabasesDatamodel.mdf' ); GO ALTER DATABASE model MODIFY FILE ( NAME = modellog, FILENAME = N'C:System DatabasesLogmodellog.ldf' ); GO ALTER DATABASE msdb MODIFY FILE ( NAME = msdbdata, FILENAME = N'C:System DatabasesDatamsdbdata.mdf' ); GO ALTER DATABASE msdb MODIFY FILE ( NAME = msdblog, FILENAME = N'C:System DatabasesLogmsdblog.ldf' ); GO USE model GO sp_helpfile GO USE msdb GO sp_helpfile GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

USE master;

GO

ALTER DATABASE model

MODIFY FILE

(

NAME = modeldev,

FILENAME = N'C:System DatabasesDatamodel.mdf'

);

GO

ALTER DATABASE model

MODIFY FILE

(

NAME = modellog,

FILENAME = N'C:System DatabasesLogmodellog.ldf'

);

GO

ALTER DATABASE msdb

MODIFY FILE

(

NAME = msdbdata,

FILENAME = N'C:System DatabasesDatamsdbdata.mdf'

);

GO

ALTER DATABASE msdb

MODIFY FILE

(

NAME = msdblog,

FILENAME = N'C:System DatabasesLogmsdblog.ldf'

);

GO

USE model

GO

sp_helpfile

GO

USE msdb

GO

sp_helpfile

GO

moving-databases-4

Transact-SQL

'The file "modeldev" has been modified in the system catalog. The new path will be used the next time the database is started. The file "modellog" has been modified in the system catalog. The new path will be used the next time the database is started. The file "msdbdata" has been modified in the system catalog. The new path will be used the next time the database is started. The file "msdblog" has been modified in the system catalog. The new path will be used the next time the database is started.'

1

2

3

4

5

6

7

8

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

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

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

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

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

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

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

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

We can see that the location of both databases has changed and we can see that the massage indicates that the new path will be used after restarting the service.

Now we can stop SQL Server service to move the model and msdb database files to the new location. We can stop the service in services.msc (write services.msc in the run window and click ok, find SQL Server service, right click on the service and choose stop) or in Management studio (right click on instance and choose stop) or by writing NET STOP MSSQLSERVER  (NET STOP MSSQL$instancename if you have named instance) in the command prompt.  Don’t forget to stop SQL Server Agent too.

After stopping SQL SERVER service, we can move database files from old location to the new one. Be careful to move files to the exact same location as you wrote in the ALTER DATABASE statement.

Start the services (SQL Server and SQL Server Agent) the same way you stopped them. If you stopped the instance in the command prompt write NET START MSSQLSERVER  (NET START MSSQL$instancename if you have named instance).  Check that the tempdb, the model and the msdb work.

To check tempdb, create a temporary table and try to insert data into it and then select from it. Don’t forget to drop it after the check. Now you can go and delete old tempdb files on the old location. To check msdb you can run a job or if you have database mail you can send test the mail.

Second way to move msdb and model databases, is to detach them, move the files to new location and reattach them. To that we must add T3608 trace flag to startup parameters of SQL Server. To add the flag we can go to start > Program Files > Sql Server 2005 > Configuration Tools > SQL Server Configuration Manager. In Configuration manager choose Sql Server 2005 services. In SQL Server 2005 services choose SQL Server service, right click on it and choose properties. In the properties window, go to advanced tab and open startup parameters.