資料庫卸離與附加 (SQL Server)Database Detach and Attach (SQL Server)

您可以將資料庫的資料和交易記錄檔卸離,然後再重新附加至相同或不同的 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.

Security Security

檔案存取權限是在數個資料庫作業期間設定,包括卸離或附加資料庫。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. 在卸離資料庫之前,您必須先執行 sp_replicationdboption以停用發行。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. 如需詳細資訊,請參閱 卸除資料庫快照集 (Transact-SQL)執行個體。For more information, see Drop a Database Snapshot (Transact-SQL).

    注意

    無法卸離或附加資料庫快照集。A database snapshot cannot be detached or attached.

  • 資料庫正在資料庫鏡像工作階段中進行鏡像。The database is being mirrored in a database mirroring session.

    除非工作階段結束,否則,您無法卸離資料庫。The database cannot be detached unless the session is terminated. 如需詳細資訊,請參閱移除資料庫鏡像 (SQL Server)For more information, see Removing Database Mirroring (SQL Server).

  • 資料庫受質疑。The database is suspect. 您無法卸離受質疑的資料庫,而必須先將受質疑的資料庫設定為緊急模式,才能將其卸離。A suspect database cannot be detached; before you can detach it, you must put it into emergency mode. 如需有關如何使資料庫進入緊急模式的詳細資訊,請參閱 ALTER DATABASE (Transact-SQL)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. 如需詳細資訊,請參閱差異備份 (SQL Server)For more information, see Differential Backups (SQL Server).

回應卸離錯誤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:

  1. 重新附加所有與資料庫關聯的檔案,而非只有主要檔案。Reattach all files associated with the database, not just the primary file.

  2. 解決造成錯誤訊息的問題。Resolve the problem that caused the error message.

  3. 再次卸離資料庫。Detach the database again.

附加資料庫 Attaching a Database

您可以附加複製的或卸離的 SQL ServerSQL Server 資料庫。You can attach a copied or detached SQL ServerSQL Server database. 當您將包含全文檢索目錄檔案的 SQL Server 2005SQL Server 2005 資料庫附加至 SQL Server 2017SQL Server 2017 伺服器執行個體時,系統就會從先前的位置附加這些目錄檔案以及其他資料庫檔案,此行為與 SQL Server 2005SQL Server 2005的行為相同。When you attach a SQL Server 2005SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2017SQL Server 2017 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server 2005SQL Server 2005. 如需詳細資訊,請參閱 升級全文檢索搜尋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 EngineDatabase Engine 會假設該資料庫也是唯讀的。If the primary data file being attached is read-only, the Database EngineDatabase 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. 如需詳細資訊,請參閱 CREATE MASTER KEY (Transact-SQL)ALTER MASTER KEY (Transact-SQL)For more information, see CREATE MASTER KEY (Transact-SQL) and ALTER MASTER KEY (Transact-SQL).

要不要附加記錄檔,其需求有一部分視資料庫是可讀寫或唯讀而定,如下所示: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 EngineDatabase Engine 會假設該資料庫也是唯讀的。If the primary data file being attached is read-only, the Database EngineDatabase 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. 「差異基底」 (Differential Base) 是資料庫或資料庫之檔案或檔案群組子集中所有資料的最新完整備份。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. 如需差異備份的相關資訊,請參閱差異備份 (SQL Server)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. 如需如何啟用鏈結的相關資訊,請參閱 跨資料庫擁有權鏈結伺服器組態選項For information about how to enable chaining, see cross db ownership chaining Server Configuration Option. 同時,每當附加資料庫時,TRUSTWORTHY 都會設為 OFF。Also, TRUSTWORTHY is set to OFF whenever the database is attached. 如需如何將 TRUSTWORTHY 設成 ON 的資訊,請參閱 ALTER DATABASE (Transact-SQL)For information about how to set TRUSTWORTHY to 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 ServerSQL Server 所建立的資料庫無法附加在舊版本中。A database created by a more recent version of SQL ServerSQL Server cannot be attached in earlier versions.

將資料庫附加至另一個伺服器執行個體時,為了提供一致的經驗給使用者和應用程式,您可能會需要在其他伺服器執行個體上為資料庫重新建立部分或所有中繼資料,例如登入和作業。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. 如需詳細資訊,請參閱在另一個伺服器執行個體上提供可用的資料庫時,管理中繼資料 (SQL Server)For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server).

若要卸離資料庫To detach a database

另請參閱See Also

資料庫檔案與檔案群組Database Files and Filegroups