備份及還原複寫的資料庫Back Up and Restore Replicated Databases

適用於: 是SQL Server 是Azure SQL Database (僅限受控執行個體) 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

針對複寫的資料庫進行資料的備份與還原時,需要特別地注意。Replicated databases require special attention with regards to backing up and restoring data. 本主題提供每種複寫類型的備份與還原策略之簡介資訊,以及取得詳細資訊的連結。This topic provides introductory information and links to further information on backup and restore strategies for each type of replication.

複寫支援將複寫資料庫還原到與原先建立備份的同一個伺服器與資料庫。Replication supports restoring replicated databases to the same server and database from which the backup was created. 如果您將複寫資料庫的備份還原到另一個伺服器或資料庫,將無法保留複寫設定。If you restore a backup of a replicated database to another server or database, replication settings cannot be preserved. 在這種情況下,您必須於還原備份後重新建立所有發行集與訂閱。In this case, you must recreate all publications and subscriptions after backups are restored.

注意

如果使用記錄傳送,也可以將複寫的資料庫還原至待命伺服器。It is possible to restore a replicated database to a standby server if log shipping is being used. 如需詳細資訊,請參閱記錄傳送和複寫 (SQL Server)For more information, see Log Shipping and Replication (SQL Server).

您應該定期備份複寫的資料庫及其相關聯的系統資料庫。Replicated databases and their associated system databases should be backed up regularly. 請備份下列資料庫:Back up the following databases:

  • 發行者端的發行集資料庫The publication database at the Publisher

  • 散發者端的散發資料庫The distribution database at the Distributor

  • 每個訂閱者端的訂閱資料庫The subscription database at each Subscriber

  • 發行者、散發者及所有訂閱者端的 mastermsdb 系統資料庫。The master and msdb system databases at the Publisher, Distributor and all Subscribers. 這些資料庫應與其他每個及相關的複寫資料庫同時備份。These databases should be backed up at the same time as each other and the relevant replication database. 例如,在您備份發行集資料庫的同時,在發行者端備份 mastermsdb 資料庫。For example, back up the master and msdb databases at the Publisher at the same time you back up the publication database. 還原發行集資料庫時,請確定 mastermsdb 資料庫的複寫組態與設定和發行集資料庫一致。If the publication database is restored, ensure that the master and msdb database are consistent with the publication database in terms of replication configuration and settings.

如果您執行一般記錄備份,就必須在記錄備份中擷取任何複寫相關的變更。If you perform regular log backups, any replication-related changes should be captured in the log backups. 如果不執行記錄備份,則每當與複寫相關的設定發生變更時,便應該執行備份。If you do not perform log backups, a backup should be performed whenever a setting relevant to replication is changed. 如需詳細資訊,請參閱 Common Actions Requiring an Updated BackupFor more information, see Common Actions Requiring an Updated Backup.

備份與還原策略Backup and Restore Strategies

備份與還原複寫拓撲中每個節點的策略,會因所用的複寫類型而異。The strategies for backing up and restoring each node in a replication topology differ according to the type of replication used. 如需每種複寫類型的備份與還原策略之詳細資訊,請參閱下列主題:For information on backup and restore strategies for each type of replication, see the following topics:

請隨時將一份您現行複寫設定的指令碼存放在安全之處,作為復原策略的一部份。As part of any recovery strategy, always keep a current script of your replication settings in a safe location. 如此一來,倘若伺服器發生錯誤或需要設定測試環境,您可藉由變更伺服器名稱參考來修改該指令碼,如此可幫助重新建立您的複寫設定。In the event of server failure or the need to set up a test environment, you can modify the script by changing server name references, and it can be used to help recreate your replication settings. 除了將現行複寫設定撰寫成指令碼之外,您還必須撰寫可啟用及停用複寫的指令碼。In addition to scripting your current replication settings, you should script the enabling and disabling of replication. 如需有關編寫複寫物件指令碼的詳細資訊,請參閱< Scripting Replication>。For information about scripting replication objects, see Scripting Replication.

另請參閱See Also

SQL Server 資料庫的備份和還原 Back Up and Restore of SQL Server Databases
Best Practices for Replication AdministrationBest Practices for Replication Administration