This article describes how to change the location of the data files and the log files for any Microsoft SQL Server 2005, SQL Server 2000, or SQL Server 7.0 database.
For more information about how to move system databases in SQL Server 2008, see the "Moving System Databases" topic in SQL Server Books Online. To view this topic, go to the
Move System Databases
(http://msdn2.microsoft.com/en-us/library/ms345408.aspx)
Microsoft Developer Network (MSDN) website.
The steps that you must follow to change the location for some SQL Server system databases differ from the steps that you must follow to change the location for user databases. These special cases are described separately.
Note SQL Server 7.0 system databases are incompatible with SQL
Server 2000. Do not attach SQL Server 7.0
master,
model,
msdb or distribution databases to SQL Server 2000. If you are using
SQL Server 2005, you can only attach databases of SQL Server 2005 to an
instance. All the examples in this article assume that SQL Server is installed
in the D:\Mssql7 folder. Additionally, the examples assume that all data files
and log files are located in the default D:\Mssql7\Data folder. The examples
move the data files and the log files for all the databases to the E:\Sqldata
folder.
The default data locations for SQL Server 2005 and SQL Server 2000 are as follows:
Prerequisites
- Back up all databases, especially the master database, from their current location.
- Make sure that you have system administrator (sa) permissions.
- Make sure that you know the name and the current location of all data files and log files for the database.
Note You can determine the name and the current location of all files
that a database uses by using the sp_helpfile stored procedure:use <database_name>
go
sp_helpfile
go
- You should have exclusive access to the database that you
are moving. If you have problems during the process, and if you cannot
access a database that you have moved or if you cannot start SQL Server,
examine the SQL Server error log and SQL Server Books Online for more
information about the errors that you are experiencing.
Moving user databases
The following example moves a database that is named
mydb. This database contains one data file, Mydb.mdf, and one log
file, Mydblog.ldf. If the database that you are moving has more data files or
log files, specify the files in a comma-delimited list in the
sp_attach_db stored procedure. The
sp_detach_db procedure does not change regardless of how many files the
database contains because the
sp_detach_db procedure does not list the files.
- Start SQL Server 2005 Management Studio. To do this, click Start, click All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.
- Click New Query, and then detach the database as follows:
use master
go
sp_detach_db 'mydb'
go
- Copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
- Reattach the database. Point to the files in the new
location as follows:
use master
go
sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'
go
use mydb
go
sp_helpfile
go
Note Microsoft Knowledge Base article 922804 describes an issue for SQL Server 2005 databases on a network-attached storage. For more information, click the
following article number to view the article in the Microsoft Knowledge Base:
922804
(http://support.microsoft.com/kb/922804/
)
FIX: After you detach a Microsoft SQL Server 2005 database that resides on network-attached storage, you cannot reattach the SQL Server database
Consider this issue. Additionally, consider the
permissions that are applied to a database when it is detached in SQL Server
2005. For more information, see the "Detaching and Attaching a Database"
section of the "Securing Data and Log Files" topic in SQL Server Books Online.
To view this topic, go to the
Securing Data and Log Files
(http://msdn2.microsoft.com/en-us/library/ms189128.aspx)
MSDN website.
Moving sample databases
To move the pubs sample database and the Northwind sample database in SQL Server 2000 or SQL Server 7.0, or to move the AdventureWorks sample database and the AdventureWorksDW sample database in SQL Server 2005, follow the same procedure for moving user databases.
Moving the model database
SQL Server 2005 and SQL Server 2000
In SQL Server 2005 and SQL Server 2000, you cannot detach system databases by using the
sp_detach_db stored procedure. When you try to run the
sp_detach_db 'model' statement, you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System
databases master, model, msdb, and tempdb cannot be detached.
To move the
model database, you must start SQL Server together with the
-c option, the
-m option, and trace flag 3608. Trace flag 3608 prevents SQL Server
from recovering any database except the
master database.
Note You will be unable to access any user databases after you do
this. You must not perform any operations when you use this trace flag, other than the following steps. To add trace flag 3608 as a SQL Server startup
parameter on SQL Server 2000, follow these steps:
- In SQL Server Enterprise Manager, right-click the server
name, and then click Properties.
- On the General tab, click Startup
Parameters.
- Add the following new parameter:
-c -m -T3608
If you are using SQL Server 2005, you can use SQL Server
Configuration Manager to change the startup parameters of the SQL Server
service. For more information about how to change the startup parameters, go to
the
Database Engine Service Startup Options
(http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
MSDN website.
After you add the
-c option, the
-m option, and trace flag 3608, follow these steps:
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'model'
go
- Move the Model.mdf and Modellog.ldf files from the
D:\Mssql7\Data folder to the E:\Sqldata folder.
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
- Remove -c -m -T3608 from the startup parameters in SQL Server Enterprise Manager or
in SQL Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change
in file locations by using the sp_helpfile stored procedure. For example, use the following command:
use model
go
sp_helpfile
go
SQL Server 7.0
- Make sure that the SQL Server Agent is currently not running.
- Follow the same procedure for moving user
databases.
Moving the MSDB database
SQL Server 2005 and SQL Server 2000
To move the MSDB database, you must start SQL Server together
with the
-c option, the
-m option, and trace flag 3608. Trace flag 3608 prevents SQL Server
from recovering any database except the
master database. To add the
-c option, the
-m option, and trace flag 3608, follow the steps in the "Moving the
model database" section. After you add the -c option, the -m option and trace
flag 3608, follow these steps:
- Stop and then restart SQL Server.
- Make sure that the SQL Server Agent service is currently not running.
- Detach the msdb database as follows:
use master
go
sp_detach_db 'msdb'
go
- Move the Msdbdata.mdf and Msdblog.ldf files from the
current location (D:\Mssql8\Data) to the new location
(E:\Mssql8\Data).
- Remove -c -m -T3608 from the startup parameters box in Enterprise Manager.
- Stop and then restart SQL Server.
Note If you try to reattach the msdb database by starting SQL Server together with the -c option, the -m option, and trace flag 3608, you may receive the following error
message: Server: Msg 615, Level 21, State 1, Line
1
Could not find database table ID 3, name 'model'.
- Reattach the msdb database as follows:
use master
go
sp_attach_db 'msdb','E:\Mssql8\Data\msdbdata.mdf','E:\Mssql8\Data\msdblog.ldf'
go
Note If you use this procedure together with moving the
model database, you are trying to detach the
msdb database while you detach the
model database. When you do this, you must reattach the
model database first, and then reattach the
msdb database. If you reattach the
msdb database first, you receive the following error message when you
try to reattach the
model database:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any,
should be discarded.
In this case, you must detach the
msdb database, reattach the
model database, and then reattach the
msdb database.
After you move the
msdb database, you may receive the following error message:
Error 229: EXECUTE permission denied on object
'ObjectName', database 'master', owner
'dbo'.
This problem occurs because the ownership chain is broken. The database owners for the
msdb database and for the master database are not the same. In this
case, the ownership of the
msdb database was changed. To work around this problem, run the
following Transact-SQL statements. You can do this by using the Osql.exe
command prompt utility (SQL Server 7.0 and SQL Server 2000) or the Sqlcmd.exe
command prompt utility (SQL Server 2005):
USE MSDB
Go
EXEC sp_changedbowner 'sa'
Go
For more information, click the following article number to
view the article in the Microsoft Knowledge Base:
272424
(http://support.microsoft.com/kb/272424/
)
Object ownership chain checking across databases depends on the login
that is mapped to the object owners
SQL Server 7.0
Note If you are using this procedure while you are moving the
msdb and
model databases, you must reattach the
model database first, and then reattach the
msdb database. Follow these steps:
- Make sure that the SQL Server Agent is currently not running.
- Follow the same procedure for moving user
databases.
Note If SQL Server Agent is running, the
sp_detach_db stored procedure will fail and you will receive the
following message:
Server: Msg 3702, Level 16, State 1,
Line 0
Cannot drop the database 'msdb' because it is currently in
use.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Moving the master database
SQL Server 2005
For more information about how to move the master database and the Resource database, go to the
Move System Databases
(http://msdn2.microsoft.com/en-us/library/ms345408.aspx)
MSDN website.
You may experience a failure when you move the master database and the Resource database.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:
918695
(http://support.microsoft.com/kb/918695/
)
You may experience a failure when you install SQL Server 2005 Service Pack 1 on an instance of SQL Server 2005
SQL Server 2000 and SQL Server 7.0
- Change the path for the master data files and the master
log files in SQL Server Enterprise Manager.
Note You can also change the location of the error log here. - Right-click the SQL Server in Enterprise Manager, and then click Properties.
- Click Startup Parameters to see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
file. - Change these values as follows:
- Remove the current entries for the Master.mdf and
Mastlog.ldf files.
- Add new entries specifying the new location:
-dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf
- Stop SQL Server.
- Copy the Master.mdf and Mastlog.ldf files to the new
location (E:\Sqldata).
- Restart SQL Server.
Moving the tempdb database
You can move
tempdb files by using the ALTER DATABASE statement.
- Determine the logical file names for the tempdb database by using sp_helpfile as follows:
use tempdb
go
sp_helpfile
go
- Use the ALTER DATABASE statement, specifying the logical
file name as follows:
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go
Message 1
File 'tempdev' modified in sysaltfiles.
Delete old file after restarting SQL Server.
Message 2
File 'templog' modified in sysaltfiles.
Delete old file after restarting SQL Server.
- Using sp_helpfile in tempdb will not confirm these changes until you restart SQL
Server.
- Stop and then restart SQL Server.
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
912397
(http://support.microsoft.com/kb/912397/
)
The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid
274188
(http://support.microsoft.com/kb/274188/
)
"Troubleshooting orphaned users" topic in Books Online is incomplete
246133
(http://support.microsoft.com/kb/246133/
)
How to transfer logins and passwords between instances of SQL Server
168001
(http://support.microsoft.com/kb/168001/
)
User logons and permissions on a database may be incorrect after the database is restored
For more information, see the following books:
Article ID: 224071 - Last Review: July 16, 2012 - Revision: 19.0
Applies to
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
| kbsqlsetup kbinfo KB224071 |
Back to the top
