How to use Detach and Attach functions to move SQL Server databases

Article translations Article translations
Article ID: 224071 - View products that this article applies to.
Expand all | Collapse all

On This Page

Summary

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.

More information

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.
  1. 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.
  2. Click New Query, and then detach the database as follows:
    use master
       go
       sp_detach_db 'mydb'
       go
  3. Copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
  4. 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
    Verify the change in file locations by using the sp_helpfile stored procedure:
    use mydb
       go
       sp_helpfile
       go
    The filename column values should reflect the new locations.
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 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 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

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.
  1. Determine the logical file names for the tempdb database by using sp_helpfile as follows:
    use tempdb
    go
    sp_helpfile
    go
    The logical name for each file is contained in the name column. This example uses the default file names of tempdev and templog.
  2. 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
    You should receive the following messages that confirm the change:
    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.
  3. Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
  4. Stop and then restart SQL Server.

References

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
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 "Troubleshooting orphaned users" topic in Books Online is incomplete
246133 How to transfer logins and passwords between instances of SQL Server
168001 User logons and permissions on a database may be incorrect after the database is restored

For more information, see the following books:
Microsoft Corporation
Microsoft SQL Server 7.0 System Administration Training Kit
Microsoft Press, 2001
Microsoft Corporation
MCSE Training Kit: Microsoft SQL Server 2000 System Administration
Microsoft Press, 2001
Microsoft Corporation
Microsoft SQL Server 2000 Resource Kit
Microsoft Press, 2001

Properties

Article ID: 224071 - Last Review: June 22, 2014 - Revision: 21.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
Keywords: 
kbsqlsetup kbinfo KB224071

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com