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

Artikel
04/01/2012

This topic describes how to use detach and attach operations to upgrade a SQL Server 2005, SQL Server 2008 or SQL Server 2008 R2 database in SQL Server 2012. After being attached to SQL Server 2012, the database is available immediately and is automatically upgraded.

In This Topic

Before you begin:

Limitations and Restrictions

Recommendations
To Upgrade a SQL Server Database:

Using detach and attach operations
Follow Up: After Upgrading a SQL Server Database

Before You Begin

Limitations and Restrictions

The system databases cannot be attached.
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 Server Configuration Option.
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).

Recommendations

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.

To Upgrade a Database by Using Detach and Attach

Detach the database. For more information, see Detach a Database.
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 2012. For more information, see Attach a Database.

Arrow icon used with Back to Top link [Top]

Example

The following example upgrades a copy of a database from an earlier version of SQL Server. The Transact-SQL statements are executed in a Query Editor window that is connected to the server instance to which is attached.

Detach the database by executing the following Transact-SQL statements:
```
USE master;
GO
EXEC sp_detach_db @dbname = N'MyDatabase';
GO
```
Using the method of your choice, copy the data and log files to the new location.

Important

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

To copy files over the network to a disk on a remote computer, use the universal naming convention (UNC) name of the remote location. A UNC name takes the form **\\Servername\Sharename\Path\**Filename. As with writing files to the local hard disk, the appropriate permissions that are required to read or write to a file on the remote disk must be granted to the user account used by the instance of SQL Server.
Attach the moved database and, optionally, its log by executing the following Transact-SQL statement:
```
USE master;
GO
CREATE DATABASE MyDatabase 
    ON (FILENAME = 'C:\MySQLServer\MyDatabase.mdf'),
    (FILENAME = 'C:\MySQLServer\Database.ldf')
    FOR ATTACH;
GO
```
In SQL Server Management Studio, a newly attached database is not immediately visible in Object Explorer. To view the database, in Object Explorer, click View, and then Refresh. When the Databases node is expanded in Object Explorer, the newly attached database now appears in the list of databases.

Follow Up: After Upgrading a SQL Server Database

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.

Database Compatibility Level After Upgrade

If the compatibility level of a user database is 90 or 100 before upgrade, it remains the same after upgrade. If the compatibility level was 80 or less before upgrade, in the upgraded database, the compatibility level is set to 90, which is the lowest supported compatibility level in SQL Server 2012. For more information, see ALTER DATABASE Compatibility Level (Transact-SQL).

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 Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).

Service Master Key and Database Master Key Encryption changes from 3DES to AES

SQL Server 2012 uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). AES is a newer encryption algorithm than 3DES used in earlier versions. When a database is first attached or restored to a new instance of SQL Server, a copy of the database master key (encrypted by the service master key) is not yet stored in the server. You must use the OPEN MASTER KEY statement to decrypt the database master key (DMK). Once the DMK has been decrypted, you have the option of enabling automatic decryption in the future by using the ALTER MASTER KEY REGENERATE statement to provision the server with a copy of the DMK, encrypted with the service master key (SMK). When a database has been upgraded from an earlier version, the DMK should be regenerated to use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL). The time required to regenerate the DMK key to upgrade to AES depends upon the number of objects protected by the DMK. Regenerating the DMK key to upgrade to AES is only necessary once, and has no impact on future regenerations as part of a key rotation strategy.