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:
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
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:
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
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.
Add;-T3608 at the end of startup parameters. If you want to be in single_user mode add –m to startup parameters. Now we have to stop and start the service to save the changes. We can do it in the configuration manager: right click on SQL Server services and choose restart.
Another way to add trace flag is to stop the service and start it with new parameters in the command prompt:
NET STOP MSSQLSERVER NET START MSSQLSERVER /T3608
Before detaching the databases we have to check the location of the files so that we will know from where to move the files. To do that we can use sp_helpfile procedure as we did last time. Now we can detach model and msdb databases.
Transact-SQL
USE master GO sp_detach_db 'model' GO USE master GO sp_detach_db 'msdb' GO
1
2
3
4
5
6
7
8
USE master
GO
sp_detach_db 'model'
GO
USE master
GO
sp_detach_db 'msdb'
GO
Now we can move the files from the old location to the new one. After moving the files we can attach the databases with their new path. We must attach model database first to avoid errors.
Transact-SQL
USE master GO sp_attach_db 'model', N'C:System DatabasesDatamodel.mdf', N'C:System DatabasesLogmodellog.ldf' GO USE master GO sp_attach_db 'msdb', N'C:System DatabasesDatamsdbdata.mdf', N'C:System DatabasesLogmsdblog.ldf' GO
1
2
3
4
5
6
7
8
9
10
11
12
USE master
GO
sp_attach_db 'model',
N'C:System DatabasesDatamodel.mdf',
N'C:System DatabasesLogmodellog.ldf'
GO
USE master
GO
sp_attach_db 'msdb',
N'C:System DatabasesDatamsdbdata.mdf',
N'C:System DatabasesLogmsdblog.ldf'
GO
Now we have to check that attached databases work fine and that the files configured correctly. We can do it by executing sp_helpfile procedure for each database. DON’T forget to remove trace flag from startup parameters and restart the service. If you used the command prompt to add the trace flag you can just stop and start the service:
NET STOP MSSQLSERVER NET START MSSQLSERVER
Moving master and resource databases
In SQL Server 2005 the Resource database depends on the location of the master database. So we should move them together to the same location. You should know in SQL Server 2008 we can’t move resource database to another location. Resource database is located in C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinn and it doesn’t have to be in the same location as master database like it was in SQL Server 2005.
First we will move master database. Before moving master database we have to change the path to the new location of database files in startup parameters. To do that in SQL Server 2005 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 properties window go to advanced tab and open startup parameters.
In SQL server 2008 we can go to start > All Programs > Sql Server 2008 > Configuration Tools > SQL Server Configuration Manager. In Configuration manager choose Sql Server services. In SQL Server services choose SQL Server service, right click on it and choose properties. In properties window go to advanced tab and open startup parameters.
As we can see the startup parameters in SQL Server 2005 and 2008 are the same. There are 3 startup parameters:
-d – refers to master data file location -e – refers to error log location -l – refers to master log file location
Now we will change the path of the master data and log files:
-dC:System DatabasesDatamaster.mdf;-eC:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLLogERRORLOG;-lC:System DatabasesLogmastlog.ldf
We can also change error log location by entering new path in –e section.
After changing master database files path in startup parameters have to stop SQL Server service to save the changes and to move the files to the new location. We can stop the service in Configuration manager or services window or command prompt (NET STOP MSSQLSERVER). In SQL Server 2008 we only have to start SQL Server service. We can do it in Configuration manager or services window or command prompt (NET START MSSQLSERVER). Now we can check that the changes took place by executing sp_helpfile procedure.
In SQL Server 2005 it’s a bit complicated since we have to move the resource database: We will start SQL Server instance in master-only recovery mode by writing the next command in the command prompt:
NET START MSSQLSERVER /f /T3608 For named instance: NET START MSSQL$instancename /f/T3608
Now we can change the configuration of the resource database.
Transact-SQL
USE master GO ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = data, FILENAME = N'C:System DatabasesDatamssqlsystemresource.mdf' ); GO ALTER DATABASE mssqlsystemresource MODIFY FILE ( NAME = log, FILENAME = N'C:System DatabasesLogmssqlsystemresource.ldf' ); GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE master
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE
(
NAME = data,
FILENAME = N'C:System DatabasesDatamssqlsystemresource.mdf'
);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE
(
NAME = log,
FILENAME = N'C:System DatabasesLogmssqlsystemresource.ldf'
);
GO
We should stop the service again to move resource database file to the new location. To stop the service we can use NET STOP MSSQLSERVER command in command prompt.
After moving the files we have to set resource database to read_only mode. But first we will start the service by using the command: NET START MSSQLSERVER /f /T3608 ALTER DATABASE mssqlsystemresource SET READ_ONLY
Now we can stop and start the instance in the regular mode.
NET STOP MSSQLSERVER NET START MSSQLSERVER
For named instance:
NET STOP MSSQL$instancename NET START MSSQL$instancename
We can check that the changes in master database took place by executing sp_helpfile procedure.