How to: Upgrade a Database Using Detach and Attach (Transact-SQL)

Security Note:

We recommend that you do not attach or restore databases from unknown or untrusted sources. Such databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

In SQL Server 2005, you can use detach and attach to upgrade a user database from SQL Server version 7.0 or SQL Server 2000. However, the following restrictions apply:

• Backups of the master, model or msdb database that are created using SQL Server 7.0 or SQL Server 2000 cannot be attached in SQL Server 2005.
• SQL Server 7.0 log files that contain create-index operations cannot be attached in SQL Server 2000 or SQL Server 2005.

Important

Database backups that are created using SQL Server 6.5 or earlier are in an incompatible format and cannot be attached in SQL Server 2005.

• When attaching a replicated database that was copied instead of detached:
  • If you attach the database to an upgraded version of the same server instance, you must execute sp_vupgrade_replication to upgrade replication after the attach operation finishes. For more information, see sp_vupgrade_replication (Transact-SQL).
  • If you attach the database to a different server instance (regardless of version), you must execute sp_removedbreplication to remove replication after the attach operation finishes. For more information, see sp_removedbreplication (Transact-SQL).

When the APPLY, PIVOT, TABLESAMPLE, or UNPIVOT keywords are used against databases that are upgraded to SQL Server 2005, the compatibility level of the database must be set to 90. To set the database compatibility level, see sp_dbcmptlevel (Transact-SQL).

Important

   In SQL Server 2000 Service Pack 3 (SP3) and later versions of SQL Server, attach and detach disable cross-database ownership chaining for the database by setting its cross db ownership chaining option to 0. For information about enabling chaining, see cross db ownership chaining Option.

Procedures

To upgrade a database using detach and attach

1. Detach the database from the instance of SQL Server 7.0 or SQL Server 2000 by using the sp_detach_db stored procedure.

   For more information, see SQL Server Books Online for that version of SQL Server.

   Note

   In SQL Server 2005, this stored procedure has new options. For more information, see sp_detach_db (Transact-SQL).

2. Optionally, move the detached database file or files and the log file or files.

   You should move the log files along with the data files, even if you intend to create new log files. In some cases, reattaching a database requires its existing log files. Therefore, always keep all the detached log files until the database has been successfully attached without them.

   Note

   If you try to attach the database without specifying the log file, the attach operation will look for the log file in its original location. If the original copy of the log still exists in that location, that copy is attached. To avoid using the original log file, either specify the path of the new log file or remove the original copy of the log file (after copying it to the new location).

3. Attach the copied files to the instance of SQL Server 2005 by using the CREATE DATABASE statement with the FOR ATTACH or FOR ATTACH_REBUILD_LOG option.

   Note

   For information about how to attach a SQL Server 2005 database by using Object Explorer, see How to: Attach a Database (SQL Server Management Studio).

4. We recommend that you run DBCC UPDATEUSAGE on the upgraded database.

   In earlier versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. Therefore, databases that were created on versions prior to SQL Server 2005 may contain incorrect counts. After you upgrade a database to SQL Server 2005, we recommend that you run DBCC UPDATEUSAGE to correct any invalid counts. This DBCC statement corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. For more information, see DBCC UPDATEUSAGE (Transact-SQL).

5. Optionally, if you are making a copy of the database (instead of moving it), you can reattach the original database on the instance of SQL Server 7.0 or SQL Server 2000 by using the sp_attach_db or sp_attach_single_file_db stored procedure.

   For more information, see SQL Server Books Online for that version of SQL Server.

Managing Metadata on the Upgraded Server Instance

When you attach a database onto another server instance, to provide a consistent experience to users and applications, you might have to re-create some or all of the metadata for the database, such as logins, jobs, and permissions, on the other server instance. For more information, see Managing Metadata When Making a Database Available on Another Server Instance.

Examples

The following example upgrades the SQL Server 2000 pubs database to a SQL Server 2005 database by using Transact-SQL statements to detach and attach the database.

1. Connect SQL Server 2000 Query Analyzer to a server instance on which pubs is attached, and detach the database by using the sp_detach_db stored procedure.

   USE master;
   GO
   EXEC sp_detach_db @dbname = N'pubs';
   GO
   

2. For the purposes of this example, using the method of your choice, copy the pubs files (pubs.mdf and pubs_log.ldf) from C:\Program Files\Microsoft SQL Server\MSSQL\Data\ (this is the default locations of pubs in SQL Server 2000) to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ (this is the SQL Server 2005 data directory).

   Important

   For a production database, place the database and transaction log on separate disks.

   Note

   To copy files over the network to a disk on a remote computer using the universal naming convention (UNC) name of the remote location. A UNC name takes the form \Servername</STRONG>Sharename</STRONG>Path</STRONG>Filename. As for writing files to the local hard disk, the user account used by SQL Server must have been granted the permissions required for reading or writing to a file on the remote disk.

3. Attach the copied pubs database and, optionally, log files to an instance of SQL Server 2005 (this example uses the same database name). In SQL Server Management Studio, open a new Query Editor query and connect to the server instance on which you want to attach the database.

   Note

   For more information, see SQL Server Management Studio Transact SQL Query.

   Execute the following CREATE DATABASE statement.

   USE master;
   GO
   CREATE DATABASE pubs ON PRIMARY 
      (FILENAME = 
         'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs.mdf')
      LOG ON (FILENAME = 
         'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\pubs_log.ldf')
      FOR ATTACH;
   GO
   

   Note

   In SQL Server Management Studio, a newly attached database is not immediately visible in Object Explorer. To view the database, click the Object Explorer window, and select View > Refresh. When the Databases node is expanded, the newly attached database now appears in the list of databases.

4. Optionally, reattach the original pubs database to the instance of SQL Server 2000 by using the sp_attach_db stored procedure. In Query Analyzer, enter the following:

   USE master;
   Go
   EXEC sp_attach_db @dbname = N'pubs', 
      @filename1 = 
         N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', 
      @filename2 = 
         N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf';
   GO
   

See Also

Tasks

How to: Attach a Database (SQL Server Management Studio)
How to: Upgrade to SQL Server 2005 with the Copy Database Wizard

Concepts

Detaching and Attaching Databases
Securing Data and Log Files

Other Resources

CREATE DATABASE (Transact-SQL)
Using Upgrade Advisor to Prepare for Upgrades

Help and Information

Getting SQL Server 2005 Assistance