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

SQL Server Setup

previous page next page

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 2008, you can use detach and attach operations to upgrade a user database from SQL Server 2000 or SQL Server 2005. After you attach a SQL Server 2005 or SQL Server 2000 database to SQL Server 2008, the database becomes available immediately and is then automatically upgraded.

However, the following restrictions apply:

  • Copies of the master, model or msdb database created using SQL Server 2000 or SQL Server 2005 cannot be attached.
  • 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 from SQL Server 2000 to SQL Server 2008, the compatibility level of the database must be set to 100. 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.

Full-Text Index Upgrade Options

Note:
After you attach a SQL Server 2005 or SQL Server 2000 database to SQL Server 2008, the database becomes available immediately and is then automatically upgraded. If the database has full-text indexes, the upgrade process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. If the upgrade option is set to import (upgrade_option = 2) or rebuild (upgrade_option = 0), the full-text indexes will be unavailable during the upgrade. Depending the amount of data being indexed, importing can take several hours, and rebuilding can take up to ten times longer. Note also that when the upgrade option is set to import, the associated full-text indexes are rebuilt if a full-text catalog is not available. To change the setting of the upgrade_option server property, use sp_fulltext_service.

Procedures

To upgrade a database using detach and attach

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).

  • 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).

  • 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).

  • 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).

  • 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.
      Copy Code
      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\MSSQL10.MSSQLSERVER\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\Sharename\Path\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 Database Engine Query Editor Window.

      Execute the following CREATE DATABASE statement.
      Copy Code
      USE master;
GO
CREATE DATABASE pubs ON PRIMARY 
   (FILENAME = 
      'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\pubs.mdf')
   LOG ON (FILENAME = 
      'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\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:
      Copy Code
      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

    Concepts

    Using Upgrade Advisor to Prepare for Upgrades

    Other Resources

    Detaching and Attaching Databases
    CREATE DATABASE (Transact-SQL)
    How to: Attach a Database (SQL Server Management Studio)
    How to: Upgrade SQL Server with the Copy Database Wizard
    Securing Data and Log Files

    previous page start next page