數據庫分離和附加 (SQL Server)Database Detach and Attach (SQL Server)

06/30/2020

本文內容

適用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)適用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions)

可以分離數據庫的數據和事務日志文件，然后將它們重新附加到同一或其他 SQL ServerSQL Server實例。The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL ServerSQL Server. 如果要將數據庫更改到同一計算機的不同 SQL ServerSQL Server 實例或要移動數據庫，分離和附加數據庫會很有用。Detaching and attaching a database is useful if you want to change the database to a different instance of SQL ServerSQL Server on the same computer or to move the database.

SecuritySecurity

文件訪問權限可在很多數據庫操作過程中設置，其中包括分離或附加數據庫。File access permissions are set during a number of database operations, including detaching or attaching a database.

重要

建議您不要附加或還原來自未知或不可信源的數據庫。We recommend that you do not attach or restore databases from unknown or untrusted sources. 此類數據庫可能包含惡意代碼，這些代碼可能會執行非預期的 Transact-SQLTransact-SQL 代碼，或者通過修改架構或物理數據庫結構導致錯誤。Such databases could contain malicious code that might execute unintended Transact-SQLTransact-SQL code or cause errors by modifying the schema or the physical database structure.

使用來自未知源或不可信源的數據庫前，請在非生產服務器上針對數據庫運行 DBCC CHECKDB ，然后檢查數據庫中的代碼，例如存儲過程或其他用戶定義代碼。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.

分離數據庫Detaching a Database

分離數據庫是指將數據庫從 SQL ServerSQL Server 實例中刪除，但使數據庫在其數據文件和事務日志文件中保持不變。Detaching a database removes it from the instance of SQL ServerSQL Server but leaves the database intact within its data files and transaction log files. 之后，就可以使用這些文件將數據庫附加到任何 SQL ServerSQL Server實例，包括分離該數據庫的服務器。These files can then be used to attach the database to any instance of SQL ServerSQL Server, including the server from which the database was detached.

如果存在下列任何情況，則不能分離數據庫：You cannot detach a database if any of the following are true:

已復制并發布數據庫。The database is replicated and published. 如果進行復制，則數據庫必須是未發布的。If replicated, the database must be unpublished. Before you can detach it, you must disable publishing by running sp_replicationdboption.

備注

如果無法使用 sp_replicationdboption，可以通過運行 sp_removedbreplication刪除復制。If you cannot use sp_replicationdboption, you can remove replication by running sp_removedbreplication.

數據庫中存在數據庫快照。A database snapshot exists on the database.

必須首先刪除所有數據庫快照，然后才能分離數據庫。Before you can detach the database, you must drop all of its snapshots.

備注

不能分離或附加數據庫快照。A database snapshot cannot be detached or attached.

數據庫是 Always On 可用性組的一部分。The database is part of an Always On availability group.

在將數據庫從可用性組中刪除之前，無法分離該數據庫。The database cannot be detached until it is removed from the availability group.

該數據庫正在某個數據庫鏡像會話中進行鏡像。The database is being mirrored in a database mirroring session.

除非終止該會話，否則無法分離該數據庫。The database cannot be detached unless the session is terminated.

數據庫處于可疑狀態。The database is suspect. 無法分離可疑數據庫；必須將數據庫設為緊急模式，才能對其進行分離。A suspect database cannot be detached; before you can detach it, you must put it into emergency mode. For more information about how to put a database into emergency mode, see ALTER DATABASE (Transact-SQL).

數據庫為系統數據庫。The database is a system database.

備份、還原及分離Backup and Restore and Detach

分離只讀數據庫將會丟失有關差異備份的差異基準的信息。Detaching a read-only database loses information about the differential bases of differential backups.

響應分離錯誤Responding to Detach Errors

分離數據庫時生成的錯誤會阻止完全關閉數據庫和重新生成事務日志。Errors produced while detaching a database can prevent the database from closing cleanly and the transaction log from being rebuilt. 收到錯誤消息后，請執行下列更正操作：If you receive an error message, perform the following corrective actions:

重新附加與數據庫關聯的所有文件，而不僅僅是主文件。Reattach all files associated with the database, not just the primary file.

解決導致生成錯誤消息的問題。Resolve the problem that caused the error message.

再次分離數據庫。Detach the database again.

附加數據庫Attaching a Database

您可以附加復制的或分離的 SQL ServerSQL Server 數據庫。You can attach a copied or detached SQL ServerSQL Server database. 當將包含全文目錄文件的 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 數據庫附加到 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 服務器實例上時，會將目錄文件從其以前的位置與其他數據庫文件一起附加，這與 SQL Server 2005 (9.x)SQL Server 2005 (9.x)中的情況相同。When you attach a SQL Server 2005 (9.x)SQL Server 2005 (9.x) database that contains full-text catalog files onto a SQL Server 2019 (15.x)SQL Server 2019 (15.x) server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005 (9.x)SQL Server 2005 (9.x). 有關詳細信息，請參閱 全文搜索升級。For more information, see Upgrade Full-Text Search.

附加數據庫時，所有數據文件(MDF 文件和 NDF 文件)都必須可用。When you attach a database, all data files (MDF and NDF files) must be available. 如果任何數據文件的路徑不同于首次創建數據庫或上次附加數據庫時的路徑，則必須指定文件的當前路徑。If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.

備注

如果附加的主數據文件是只讀的，則 數據庫引擎Database Engine 假定數據庫也是只讀的。If the primary data file being attached is read-only, the 數據庫引擎Database Engine assumes that the database is read-only.

當加密的數據庫首次附加到 SQL ServerSQL Server 實例時，數據庫所有者必須通過執行下面的語句打開數據庫的主密鑰：OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'。When an encrypted database is first attached to an instance of SQL ServerSQL Server, the database owner must open the master key of the database by executing the following statement: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'. 建議通過執行下面的語句對主密鑰啟用自動解密：ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY。We recommend that you enable automatic decryption of the master key by executing the following statement: ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY.

附加日志文件的要求在某些方面取決于數據庫是讀寫的還是只讀的，如下所示：The requirement for attaching log files depends partly on whether the database is read-write or read-only, as follows:

對于讀寫數據庫，通常可以附加新位置中的日志文件。For a read-write database, you can usually attach a log file in a new location. 不過，在某些情況下，重新附加數據庫需要使用其現有的日志文件。However, in some cases, reattaching a database requires its existing log files. 因此，請務必保留所有分離的日志文件，直到在不需要這些日志文件的情況下成功附加了數據庫。Therefore, it is important to always keep all the detached log files until the database has been successfully attached without them.

如果讀寫數據庫具有單個日志文件，并且您沒有為該日志文件指定新位置，附加操作將在舊位置中查找該文件。If a read-write database has a single log file and you do not specify a new location for the log file, the attach operation looks in the old location for the file. 如果找到了舊日志文件，則無論數據庫上次是否完全關閉，都將使用該文件。If it is found, the old log file is used, regardless of whether the database was shut down cleanly. 但是，如果未找到舊文件日志，數據庫上次是完全關閉且現在沒有活動日志鏈，則附加操作將嘗試為數據庫創建新的日志文件。However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database.

如果附加的主數據文件是只讀的，則 數據庫引擎Database Engine 假定數據庫也是只讀的。If the primary data file being attached is read-only, the 數據庫引擎Database Engine assumes that the database is read-only. 對于只讀數據庫，日志文件在數據庫主文件中指定的位置上必須可用。For a read-only database, the log file or files must be available at the location specified in the primary file of the database. 因為 SQL ServerSQL Server 無法更新主文件中存儲的日志位置，所以無法生成新的日志文件。A new log file cannot be built because SQL ServerSQL Server cannot update the log location stored in the primary file.

附加數據庫時的元數據更改Metadata Changes on Attaching a Database

分離再重新附加只讀數據庫后，會丟失有關當前差異基準的備份信息。When a read-only database is detached and then reattached, the backup information about the current differential base is lost. “差異基準” 是數據庫或其文件或文件組子集中所有數據的最新完整備份。The differential base is the most recent full backup of all the data in the database or in a subset of the files or filegroups of the database. 如果沒有基準備份信息， master 數據庫會變得與只讀數據庫不同步，這樣之后進行的差異備份可能會產生意外結果。Without the base-backup information, the master database becomes unsynchronized with the read-only database, so differential backups taken thereafter may provide unexpected results. 因此，如果對只讀數據庫使用差異備份，在重新附加數據庫后，應通過進行完整備份來建立新的差異基準。Therefore, if you are using differential backups with a read-only database, you should establish a new differential base by taking a full backup after you reattach the database. For information about differential backups, see Differential Backups (SQL Server).

附加時，數據庫會啟動。On attach, database startup occurs. 通常，附加數據庫時會將數據庫重置為它分離或復制時的狀態。Generally, attaching a database places it in the same state that it was in when it was detached or copied. 但是，附加和分離操作都會禁用數據庫的跨數據庫所有權鏈接。However, attach-and-detach operations both disable cross-database ownership chaining for the database.

重要

為安全起見，，默認情況下，每當附加數據庫時，is_broker_enabled、is_honoor_broker_priority_on 和 is_trustworthy_on 的選項均設置為 OFF 。By default and for security, the options for is_broker_enabled, is_honor_broker_priority_on and is_trustworthy_on are set to OFF whenever the database is attached. For information about how to set these options on, see ALTER DATABASE (Transact-SQL).

備份、還原及附加Backup and Restore and Attach

與任何完全或部分脫機的數據庫一樣，不能附加正在還原文件的數據庫。Like any database that is fully or partially offline, a database with restoring files cannot be attached. 如果停止了還原順序，則可以附加數據庫。If you stop the restore sequence, you can attach the database. 然后，可以重新啟動還原順序。Then, you can restart the restore sequence.

將數據庫附加到其他服務器實例Attaching a Database to Another Server Instance

重要

無法在早期版本的 SQL Server 中附加由較新版本的 SQL ServerSQL Server 創建的數據庫。A database created by a more recent version of SQL ServerSQL Server cannot be attached in earlier versions. 這將阻止數據庫被舊版本的 數據庫引擎Database Engine 物理使用。This precludes the database from being physically used with an older version of the 數據庫引擎Database Engine. 但是，這與元數據狀態相關且不會影響數據庫兼容性級別。However, this relates to metadata state and does not affect the database compatibility level.

將數據庫附加到其他服務器實例時，為了給用戶和應用程序提供一致的體驗，您最好在其他服務器實例上為數據庫重新創建部分或全部元數據(例如登錄名和作業)。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 and jobs, on the other server instance.

相關任務Related Tasks

分離數據庫To detach a database

附加數據庫To attach a database

使用分離和附加操作升級數據庫To upgrade a database using detach and attach operations

使用分離和附加操作移動數據庫To move a database using detach and attach operations

刪除數據庫快照To delete a database snapshot

另請參閱See Also

總結

以上是生活随笔為你收集整理的mysql数据库的分离_数据库分离和附加 (SQL Server)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。