为镜像准备镜像数据库 (SQL Server)Prepare a Mirror Database for Mirroring (SQL Server)

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

在数据库镜像会话开始之前,数据库所有者或系统管理员必须确保已创建镜像数据库并可进行镜像。Before a database mirroring session can start, the database owner or system administrator must make sure that the mirror database has been created and is ready for mirroring. 创建新镜像数据库的最低要求是:执行主体数据库的完整备份和一个后续日志备份,并使用 WITH NORECOVERY 将这两个备份还原到镜像服务器实例上。Creating a new mirror database minimally requires taking a full backup of the principal database and a subsequent log backup and restoring them both onto the mirror server instance, using WITH NORECOVERY.

本主题说明如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中准备镜像数据库。This topic describes how to prepare a mirror database in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

开始之前Before You Begin

要求Requirements

  • 主体服务器和镜像服务器实例必须运行在相同版本的 SQL ServerSQL Server上。The principal and mirror server instances must be running on the same version of SQL ServerSQL Server. 尽管镜像服务器可以具有更高版本的 SQL Server,但仅在仔细计划的升级过程中建议此配置。While it is possible for the mirror server to have a higher version of SQL Server, this configuration is only recommended during a carefully planned upgrade process. 在此类配置中,会面临自动故障转移的风险,数据移动在其中会被自动挂起,因为数据不能移到更低版本的 SQL Server。In such a configuration, you run the risk of an automatic failover, in which data movement is automatically suspended because data cannot move to a lower version of SQL Server. 有关详细信息,请参阅 Upgrading Mirrored InstancesFor more information, see Upgrading Mirrored Instances.

  • 主体服务器和镜像服务器实例必须运行在相同版本的 SQL ServerSQL Server上。The principal and mirror server instances must be running on the same edition of SQL ServerSQL Server. 有关 SQL ServerSQL Server 中数据库镜像支持的信息,请参阅 SQL Server 2017 的各版本和支持的功能For information about support for database mirroring in SQL ServerSQL Server, see Editions and Supported features of SQL Server 2017.

  • 数据库必须使用完整恢复模式。The database must use the full recovery model.

    有关详细信息,请参阅查看或更改数据库的恢复模式 (SQL Server)sys.databases (Transact-SQL)ALTER DATABASE (Transact-SQL)For more information, see View or Change the Recovery Model of a Database (SQL Server) or sys.databases (Transact-SQL) and ALTER DATABASE (Transact-SQL).

  • 镜像数据库的名称必须与主体数据库的名称相同。The name of the mirror database must be the same as the name of the principal database.

  • 为使镜像正常运行,镜像数据库必须处于 RESTORING 状态。The mirror database must be in the RESTORING state for mirroring to work. 准备镜像数据库时,对于每个还原操作都必须使用 RESTORE WITH NORECOVERY。When preparing a mirror database, you must use RESTORE WITH NORECOVERY for every restore operation. 至少,需要还原主体数据库的完整备份(WITH NORECOVERY),之后进行所有后续的日志备份。Minimally, you will need to restore WITH NORECOVERY a full backup of the principal database, followed by all subsequent log backups.

  • 计划创建镜像数据库的系统的磁盘驱动器空间必须足以容纳镜像数据库。The system where you plan to create the mirror database must possesses a disk drive with sufficient space to hold the mirror database.

限制和局限Limitations and Restrictions

  • 不能镜像 mastermsdbtempmodel 系统数据库。You cannot mirror the master, msdb, temp, or model system databases.

  • 不能镜像属于 AlwaysOn 可用性组的数据库。You cannot mirror a database that belongs to an Always On availability group.

建议Recommendations

  • 使用最近的主体数据库的完整数据库备份或最近的差异数据库备份。Use a very recent full database backup or a recent differential database backup of the principal database.

  • 如果计划在主体数据库中非常频繁地运行日志备份作业,则可能需要禁用备份作业,直到镜像启动为止。If a log backup job is scheduled to run very frequently on the principal database, you might have to disable the backup job until mirroring has started.

  • 如有可能,镜像数据库的路径(包括驱动器号)应该与主体数据库的路径相同。If possible, the path (including the drive letter) of the mirror database should be identical to the path of the principal database.

    如果文件路径必须互不相同(例如,如果主体数据库位于“F:”驱动器上,但镜像系统没有“F:”驱动器),则必须在 RESTORE 语句中加入 MOVE 选项。If the file paths must differ, for example, if the principal database is on drive 'F:' but the mirror system lacks an F: drive, you must include the MOVE option in the RESTORE STATEMENT.

    重要

    在不影响会话的情况下,在镜像会话过程中添加文件要求该文件路径同时存在于两个服务器上。Adding a file during a mirroring session without impacting the session requires that the path of the file exists on both servers. 因此,如果在创建镜像数据库时移动了数据库文件,则随后在镜像数据库上的添加文件操作可能会失败,并可能会导致镜像挂起。Therefore, if you move the database files when creating the mirror database, a later add-file operation might fail on the mirror database and cause mirroring to be suspended. 有关处理失败的创建文件操作的详细信息,请参阅数据库镜像配置故障排除 (SQL Server)For information about dealing with a failed create-file operation, see Troubleshoot Database Mirroring Configuration (SQL Server).

  • 如果主体数据库具有任何全文目录,建议参阅数据库镜像和全文目录 (SQL Server)If the principal database has any full-text catalogs, we recommend that you see Database Mirroring and Full-Text Catalogs (SQL Server).

  • 对于生产数据库,始终备份到单独的设备。For a production database, always back up to a separate device.

SecuritySecurity

备份数据库时,TRUSTWORTHY 设置为 OFF。TRUSTWORTHY is set to OFF when a database is backed up. 因此,在新的镜像数据库中,TRUSTWORTHY 始终为 OFF。Therefore, TRUSTWORTHY is always OFF on a new mirror database. 如果数据库在故障转移之后需要得到信任,则必须执行其他设置步骤。If the database needs to be trustworthy after a failover, additional setup steps are necessary. 有关详细信息,请参阅 将镜像数据库设置为使用 Trustworthy 属性 (Transact-SQL)中准备镜像数据库。For more information, see Set Up a Mirror Database to Use the Trustworthy Property (Transact-SQL).

有关启用镜像数据库主秘钥自动加密的详细信息,请参阅 设置加密的镜像数据库For information about enabling automatic decryption of the database master key of a mirror database, see Set Up an Encrypted Mirror Database.

权限Permissions

数据库所有者或系统管理员。Database owner or system administrator.

准备现有镜像数据库以重新启动镜像To Prepare an Existing Mirror Database to Restart Mirroring

如果已删除镜像,并且该镜像数据库仍处于 RECOVERING 状态,则可以重新启动镜像。If mirroring has been removed and the mirror database is still in the RECOVERING state, you can restart mirroring.

  1. 至少进行主体数据库的一个日志备份。Take at least one log backup on the principal database. 有关详细信息,请参阅 备份事务日志 (SQL Server)数据库还原到一个新位置并且可以选择重命名该数据库。For more information, see Back Up a Transaction Log (SQL Server).

  2. 在镜像数据库中,使用 RESTORE WITH NORECOVERY 还原删除镜像后在主体数据库中执行的所有日志备份。On the mirror database, use RESTORE WITH NORECOVERY to restore all log backups taken on the principal database since mirroring was removed. 有关详细信息,请参阅 还原事务日志备份 (SQL Server)中准备镜像数据库。For more information, see Restore a Transaction Log Backup (SQL Server).

准备新的镜像数据库To Prepare a New Mirror Database

准备镜像数据库To prepare a mirror database

备注

有关此过程的 Transact-SQLTransact-SQL 示例,请参阅本节后面的 示例 (Transact-SQL)For a Transact-SQLTransact-SQL example of this procedure, see Example (Transact-SQL), later in this section.

  1. 连接到主体服务器实例。Connect to principal server instance.

  2. 创建主体数据库的完整数据库备份或差异数据库备份。Create either a full database backup or a differential database backup of the principal database.

  3. 一般您需要至少进行主体数据库的一个日志备份。Typically, you need to take at least one log backup on the principal database. 但是,如果数据库刚刚创建而尚未进行日志备份,或者如果恢复模式刚刚从 SIMPLE 更改为 FULL,则不必进行日志备份。However, a log backup might be unnecessary, if the database has just been created and no log backup has been taken yet, or if the recovery model has just been changed from SIMPLE to FULL.

  4. 除非备份是在从两个系统均可访问的网络驱动器上,否则将数据库备份和日志备份复制到将承载镜像服务器实例的系统。Unless the backups are on a network drive that is accessible from both systems, copy the database and log backups to the system that will host the mirror server instance.

  5. 连接到镜像服务器实例。Connect to mirror server instance.

  6. 使用 RESTORE WITH NORECOVERY,通过还原完整数据库备份和最近的差异数据库备份(后者为可选项)到镜像服务器实例,来创建镜像数据库。Using RESTORE WITH NORECOVERY, create the mirror database by restoring the full database backup and, optionally, the most recent differential database backup, onto the mirror server instance.

    备注

    如果要逐个文件组地还原数据库,则要确保还原整个数据库。If you restore the database filegroup by filegroup, be sure to restore the whole database.

  7. 使用 RESTORE WITH NORECOVERY,将所有未完成的日志备份应用到镜像数据库。Using RESTORE WITH NORECOVERY, apply any outstanding log backup or backups to the mirror database.

示例 (Transact-SQL)Example (Transact-SQL)

必须先创建镜像数据库,才能启动数据库镜像会话。Before you can start a database mirroring session, you must create the mirror database. 应该在启动镜像会话之前执行此操作。You should do this just before starting the mirroring session.

此示例使用了 AdventureWorks2012AdventureWorks2012 示例数据库,默认情况下,该数据库使用简单恢复模式。This example uses the AdventureWorks2012AdventureWorks2012 sample database, which uses the simple recovery model by default.

  1. 若要对 AdventureWorks2012AdventureWorks2012 数据库使用数据库镜像,请改用完整恢复模式:To use database mirroring with the AdventureWorks2012AdventureWorks2012 database, modify it to use the full recovery model:

    USE master;  
    GO  
    ALTER DATABASE AdventureWorks   
    SET RECOVERY FULL;  
    GO  
    
  2. 将数据库的恢复模式从 SIMPLE 更改为 FULL 之后,创建一个完整备份,以用于创建镜像数据库。After modifying the recovery model of the database from SIMPLE to FULL, create a full backup, which can be used to create the mirror database. 由于恢复模式已更改,因此指定了 WITH FORMAT 选项来创建新的介质集。Because the recovery model has just been changed, the WITH FORMAT option is specified to create a new media set. 这对区分完整恢复模式下的备份与以前在简单恢复模式下创建的备份非常有用。This is useful to separate the backups under the full recovery model from any previous backups made under the simple recovery model. 为了实现此示例的目的,在数据库所在的同一驱动器上创建备份文件 (C:\AdventureWorks.bak)。For the purpose of this example, the backup file (C:\AdventureWorks.bak) is created on the same drive as the database.

    备注

    对于生产数据库,应始终备份到单独的设备。For a production database, you should always back up to a separate device.

    在主体服务器实例 ( PARTNERHOST1) 上,创建主体数据库的完整备份,如下所示:On the principal server instance (on PARTNERHOST1), create a full backup of the principal database as follows:

    BACKUP DATABASE AdventureWorks   
        TO DISK = 'C:\AdventureWorks.bak'   
        WITH FORMAT  
    GO  
    
  3. 将完整备份复制到镜像服务器。Copy the full backup to the mirror server.

  4. 使用 RESTORE WITH NORECOVERY,将完整备份还原到镜像服务器实例。Using RESTORE WITH NORECOVERY, restore the full backup onto the mirror server instance. 还原命令取决于主体数据库与镜像数据库的路径是否相同。The restore command depends on whether the paths of principal and mirror databases are identical.

    • 如果路径相同:If the paths are identical:

      PARTNERHOST5的镜像服务器实例上,还原完整备份,如下所示:On the mirror server instance (on PARTNERHOST5), restore the full backup as follows:

      RESTORE DATABASE AdventureWorks   
          FROM DISK = 'C:\AdventureWorks.bak'   
          WITH NORECOVERY  
      GO  
      
    • 如果路径不同:If the paths differ:

      如果镜像数据库的路径与主体数据库的路径不同(例如,它们所在的驱动器号不同),则创建镜像数据库要求还原操作包含 MOVE 子句。If the path of the mirror database differs from the path of the principal database (for instance, their drive letters differ), creating the mirror database requires that the restore operation include a MOVE clause.

      重要

      如果主体数据库与镜像数据库的路径名称不同,则无法添加文件。If the path names of the principal and mirror databases differ, you cannot add a file. 原因是在接收添加文件操作所需的日志时,镜像服务器实例尝试将新文件放置在主体数据库所在的位置。This is because on receiving the log for the add file operation, the mirror server instance attempts to place the new file in the location used by the principal database.

      例如,以下命令将位于 C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\ 中的主体数据库备份还原到镜像数据库所在的另一个位置 D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\For example, the following command restores a backup of a principal database residing in C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\ to a different location, D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\, where the mirror database is to reside.

      RESTORE DATABASE AdventureWorks  
         FROM DISK='C:\AdventureWorks.bak'  
         WITH NORECOVERY,   
            MOVE 'AdventureWorks_Data' TO   
               'D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\AdventureWorks_Data.mdf',   
            MOVE 'AdventureWorks_Log' TO  
               'D:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\AdventureWorks_Log.ldf';  
      GO  
      
  5. 创建完整备份之后,必须在主体数据库中创建日志备份。After you create the full backup, you must create a log backup on the principal database. 例如,下面的 Transact-SQLTransact-SQL 语句将日志备份到先前的完整备份所使用的文件中:For example, the following Transact-SQLTransact-SQL statement backs up the log to the same file used by the preceding full backup:

    BACKUP LOG AdventureWorks   
        TO DISK = 'C:\AdventureWorks.bak'   
    GO  
    
  6. 在开始镜像之前,必须应用必要的日志备份(以及所有后续日志备份)。Before you can start mirroring, you must apply the required log backup (and any subsequent log backups).

    例如,以下 Transact-SQLTransact-SQL 语句还原 C:\AdventureWorks.bak中的第一个日志:For example, the following Transact-SQLTransact-SQL statement restores the first log from C:\AdventureWorks.bak:

    RESTORE LOG AdventureWorks   
        FROM DISK = 'C:\AdventureWorks.bak'   
        WITH FILE=1, NORECOVERY  
    GO  
    
  7. 如果在开始镜像之前进行任何其他日志备份,则还必须使用 WITH NORECOVERY 按顺序将所有这些日志备份还原到镜像服务器上。If any additional log backups occur before you start mirroring, you must also restore all of those log backups, in sequence, to the mirror server using WITH NORECOVERY.

    例如,以下 Transact-SQLTransact-SQL 语句还原 C:\AdventureWorks.bak中的其他两个日志:For example, the following Transact-SQLTransact-SQL statement restores two additional logs from C:\AdventureWorks.bak:

    RESTORE LOG AdventureWorks   
        FROM DISK = 'C:\AdventureWorks.bak'   
        WITH FILE=2, NORECOVERY  
    GO  
    RESTORE LOG AdventureWorks   
        FROM DISK = 'C:\AdventureWorks.bak'   
        WITH FILE=3, NORECOVERY  
    GO  
    

有关设置数据库镜像、显示安全设置、准备镜像数据库、设置合作伙伴以及添加见证服务器的完整示例的信息,请参阅 设置数据库镜像 (SQL Server)中准备镜像数据库。For a complete example of setting up database mirroring, showing security setup, preparing the mirror database, setting up the partners, and adding a witness, see Setting Up Database Mirroring (SQL Server).

跟进:准备镜像数据库之后Follow Up: After Preparing a Mirror Database

  1. 如果在最近的 RESTORE LOG 操作之后已执行了任何其他日志备份,则还必须使用 RESTORE WITH NORECOVERY 手动应用其他每个日志备份。If any additional log backups have been taken since your most recent RESTORE LOG operation, you must manually apply every additional log backup, using RESTORE WITH NORECOVERY.

  2. 开始镜像会话。Start the mirroring session. 有关详细信息,请参阅 使用 Windows 身份验证建立数据库镜像会话 (SQL Server Management Studio)使用 Windows 身份验证建立数据库镜像会话 (Transact-SQL)中准备镜像数据库。For more information, see Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio) or Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL).

  3. 如果您在主体数据库上禁用了备份作业,则重新启用该作业。If you disabled the backup job on the principal database, reenable the job.

  4. 如果数据库在故障转移后需要得到信任,则必须在镜像开始后执行额外的设置步骤。If the database needs to be trustworthy after a failover, extra setup steps are necessary after mirroring begins. 有关详细信息,请参阅 将镜像数据库设置为使用 Trustworthy 属性 (Transact-SQL)中准备镜像数据库。For more information, see Set Up a Mirror Database to Use the Trustworthy Property (Transact-SQL).

另请参阅See Also

数据库镜像 (SQL Server) Database Mirroring (SQL Server)
针对数据库镜像和 AlwaysOn 可用性组的传输安全性 (SQL Server) Transport Security for Database Mirroring and Always On Availability Groups (SQL Server)
设置数据库镜像 (SQL Server) Setting Up Database Mirroring (SQL Server)
备份和还原全文目录和索引 Back Up and Restore Full-Text Catalogs and Indexes
数据库镜像和全文目录 (SQL Server) Database Mirroring and Full-Text Catalogs (SQL Server)
数据库镜像和复制 (SQL Server) Database Mirroring and Replication (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
RESTORE 参数 (Transact-SQL)RESTORE Arguments (Transact-SQL)