In this article
This topic describes how to lớn use detach và attach operations to upgrade a database in SQL Server. After being attached lớn SQL Server, the database is available immediately & is automatically upgraded. This prevents the database from being used with an older version of the Database Engine. However, metadata upgrade does not affect the database compatibility level setting of a database. See more information in Database Compatibility cấp độ After upgrade later in this topic.
In this topic
Before You Begin
Limitations & Restrictions
The system databases cannot be attached.
When attaching a replicated database that was copied instead of detached:
We recommend that you bởi vì 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 & also examine the code, such as stored procedures or other user-defined code, in the database.
to Upgrade a Database by Using Detach và Attach
Optionally, move the detached database file or files và 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.
If you try lớn attach the database without specifying the log file, the attach operation will look for the log tệp tin in its original location. If the original copy of the log still exists in that location, that copy is attached. Khổng lồ avoid using the original log file, either specify the path of the new log file or remove the original copy of the log tệp tin (after copying it lớn the new location).
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 lớn the hệ thống instance khổng lồ 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 & log files lớn the new location.
For a production database, preferably place the database và transaction log on separate disks. These drive different I/O & file growth requirements & it is considered a best practice to keep them separate.
To copy files over the network to lớn a disk on a remote computer, use the universal naming convention (UNC) name of the remote location. A UNC name takes the form \ServernameSharenamePathFilename. As with writing files khổng lồ the local hard disk, the appropriate permissions that are required to read or write to lớn a file on the remote disk must be granted lớn 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:MySQLServerMyDatabase.mdf"), (FILENAME = "C:MySQLServerDatabase.ldf") FOR ATTACH; GO In SQL server Management Studio, a newly attached database is not immediately visible in Object Explorer. Lớn view the database, in Object Explorer, click View, & then Refresh. When the Databases node is expanded in Object Explorer, the newly attached database now appears in the danh mục of databases.
Follow Up: After Upgrading a SQL server Database
If the database has full-text indexes, the nâng cấp process either imports, resets, or rebuilds them, depending on the setting of the upgrade_option server property. If the upgrade option is set lớn 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 lớn ten times longer. Note also that when the upgrade option is set khổng lồ import, the associated full-text indexes are rebuilt if a full-text catalog is not available. Khổng lồ change the setting of the upgrade_option hệ thống property, use sp_fulltext_service.
Database Compatibility level After Upgrade
After the upgrade, the database compatibility cấp độ remains at the compatibility level before the upgrade, unless the previous compatibility cấp độ is not supported on the new version. In this case, the upgraded database compatibility màn chơi is set lớn the lowest supported compatibility level.
For example, if you attach a database that was compatibility level 90 before attaching it lớn an instance of SQL server 2019 (15.x), after the tăng cấp the compatibility cấp độ is set to lớn 100, which is the lowest supported compatibility level in SQL hệ thống 2019 (15.x). 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 lớn users and applications, you might have lớn re-create some or all of the metadata for the database, such as logins, jobs, và permissions, on the other vps instance. For more information, see Manage Metadata When Making a Database Available on Another hệ thống Instance (SQL Server).
Service Master Key & Database Master Key Encryption changes from 3DES khổng lồ AES
SQL server 2012 (11.x) & higher versions uses the AES encryption algorithm lớn protect the service master key (SMK) và 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 lớn 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 lớn 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 lớn provision the vps 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 khổng lồ use the newer AES algorithm. For more information about regenerating the DMK, see ALTER MASTER KEY (Transact-SQL). The time required to lớn regenerate the DMK key to lớn upgrade lớn AES depends upon the number of objects protected by the DMK. Regenerating the DMK key lớn upgrade to lớn AES is only necessary once, & has no impact on future regenerations as part of a key rotation strategy.