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
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
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
The default data locations for SQL Server 2005 and SQL Server 2000 are as follows:
- 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:
- 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:
- 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:
Verify the change in file locations by using the sp_helpfile stored procedure:
The filename column values should reflect the new locations.
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:
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
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
SQL Server 7.0
Moving the MSDB database
SQL Server 2005 and SQL Server 2000
SQL Server 7.0
Moving the master database
SQL Server 2005
SQL Server 2000 and SQL Server 7.0
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:
The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
- Use the ALTER DATABASE statement, specifying the logical
file name as follows:
You should receive the following messages that confirm the change:
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
- Using sp_helpfile in tempdb will not confirm these changes until you restart SQL
- Stop and then restart SQL Server.
For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
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
"Troubleshooting orphaned users" topic in Books Online is incomplete
How to transfer logins and passwords between instances of SQL Server
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
- 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|