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 2008 R2, 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 or SQL Server 2008 database to SQL Server 2008 R2, 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_helpdb (Transact-SQL) (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 R2, the compatibility level of the database must be set to 100. To set the database compatibility level, see sp_dbcmptlevel (Transact-SQL).

  Wichtig

  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

Hinweis

After you attach a SQL Server 2005 or SQL Server 2000 database to SQL Server 2008 R2, 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

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.

   Hinweis

   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.

   Hinweis

   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.

   Hinweis

   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.

Database Compatibility Level After Upgrade

The compatibility levels of the tempdb, model, msdb and Resource databases are set to 100 after upgrade. The master system database retains the compatibility level it had before upgrade, unless that level was less than 80. If the compatibility level of master was less than 80 before upgrade, it is set to 80 after upgrade.

If the compatibility level of a user database was 80 or 90 before upgrade, it remains the same after upgrade. If the compatibility level was 70 or less before upgrade, in the upgraded database, the compatibility level is set to 80, which is the lowest supported compatibility level in SQL Server 2008 R2.

Hinweis

New user databases will inherit the compatibility level of the model database.

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\MSSQL10_50.MSSQLSERVER\MSSQL\Data\ (this is the SQL Server 2005 data directory).

   Wichtig

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

   Hinweis

   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.

   Hinweis

   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\MSSQL10_50.MSSQLSERVER\MSSQL\Data\pubs.mdf')
      LOG ON (FILENAME = 
         'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\pubs_log.ldf')
      FOR ATTACH;
   GO
   

   Hinweis

   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

Other Resources

Attaching and Detaching a Database

CREATE DATABASE (Transact-SQL)

How to: Attach a Database (SQL Server Management Studio)

How to: Upgrade to SQL Server 2005 with the Copy Database Wizard

Securing Data and Log Files

Using Upgrade Advisor to Prepare for Upgrades