排查使用 Azure 备份进行 SQL Server 数据库备份的问题Troubleshoot SQL Server database backup by using Azure Backup

本文针对 Azure 虚拟机上运行的 SQL Server 数据库提供故障排除信息。This article provides troubleshooting information for SQL Server databases running on Azure virtual machines.

有关备份过程和限制的详细信息,请参阅关于 Azure VM中的 SQL Server 备份For more information about the backup process and limitations, see About SQL Server backup in Azure VMs.

SQL Server 权限SQL Server permissions

若要在虚拟机上为 SQL Server 数据库配置保护,必须在该虚拟机上安装 AzureBackupWindowsWorkload 扩展。To configure protection for a SQL Server database on a virtual machine, you must install the AzureBackupWindowsWorkload extension on that virtual machine. 如果收到错误 UserErrorSQLNoSysadminMembership,则表示 SQL Server 实例没有所需的备份权限。If you get the error UserErrorSQLNoSysadminMembership, it means your SQL Server instance doesn't have the required backup permissions. 若要修复此错误,请遵循设置 VM 权限中的步骤。To fix this error, follow the steps in Set VM permissions.

错误消息Error messages

不受支持的备份类型Backup Type Unsupported

severitySeverity 说明Description 可能的原因Possible causes 建议的操作Recommended action
警告Warning 此数据库的当前设置不支持关联策略中的特定备份类型。Current settings for this database don't support certain backup types present in the associated policy.
  • 只能对 master 数据库执行完整数据库备份操作。Only a full database backup operation can be performed on the master database. 不能执行差异备份和事务日志备份。Neither differential backup nor transaction log backup is possible.
  • 简单恢复模式中的任何数据库都不允许进行事务日志备份。Any database in the simple recovery model does not allow for the backup of transaction logs.
  • 将数据库设置修改为支持策略中的所有备份类型。Modify the database settings such that all the backup types in the policy are supported. 或者,将当前策略更改为只包含受支持的备份类型。Or, change the current policy to include only the supported backup types. 否则,在计划备份期间将跳过不受支持的备份类型,或无法为临时备份执行备份作业。Otherwise, the unsupported backup types will be skipped during scheduled backup or the backup job will fail for ad hoc backup.

    UserErrorSQLPODoesNotSupportBackupTypeUserErrorSQLPODoesNotSupportBackupType

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    此 SQL 数据库不支持所请求的备份类型。This SQL database does not support the requested backup type. 当数据库恢复模式不允许所请求的备份类型时,会发生此错误。Occurs when the database recovery model doesn't allow the requested backup type. 在以下情况下,可能会发生此错误:The error can happen in the following situations:
    • 使用简单恢复模式的数据库不允许日志备份。A database that's using a simple recovery model does not allow log backup.
    • 不允许对 master 数据库执行差异备份和日志备份。Differential and log backups are not allowed for a master database.
  • 如果不想要更改恢复模式,并使用标准策略来备份无法更改的多个数据库,请忽略此错误。If you don't want to change the recovery model, and you have a standard policy to back up multiple databases that can't be changed, ignore the error. 完整备份和差异备份会按计划进行。Your full and differential backups will work per schedule. 在这种情况下,预期会跳过日志备份。The log backups will be skipped, which is expected in this case.
  • 如果备份的是 Master 数据库,并且已配置差异备份或日志备份,请使用以下任一步骤:If it's a master database and you have configured differential or log backup, use either of the following steps:
    • 使用门户将 master 数据库的备份策略计划更改为“完整”。Use the portal to change the backup policy schedule for the master database, to full.
    • 如果使用标准策略来备份无法更改的多个数据库,请忽略此错误。If you have a standard policy to back up multiple databases that can't be changed, ignore the error. 完整备份会按计划进行。Your full backup will work per schedule. 在这种情况下,预期不会发生差异备份或日志备份。Differential or log backups won't happen, which is expected in this case.
    操作将被取消,因为已对同一个数据库运行了某个有冲突的操作。Operation canceled as a conflicting operation was already running on the same database. 请参阅有关并行运行备份和还原时存在的限制的博客文章See the blog entry about backup and restore limitations that run concurrently. 使用 SQL Server Management Studio (SSMS) 监视备份作业Use SQL Server Management Studio (SSMS) to monitor the backup jobs. 有冲突的操作失败后,重启该操作。After the conflicting operation fails, restart the operation.

    UserErrorSQLPODoesNotExistUserErrorSQLPODoesNotExist

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    SQL 数据库不存在。SQL database does not exist. 该数据库已被删除或重命名。The database was either deleted or renamed. 检查是否意外删除或重命名了该数据库。Check if the database was accidentally deleted or renamed.

    如果意外删除了该数据库,若要继续备份,请将该数据库还原到原始位置。If the database was accidentally deleted, to continue backups, restore the database to the original location.

    如果删除了该数据库,且将来不需要备份,请在恢复服务保管库中选择“停止备份”和“保留备份数据”或“删除备份数据”。 If you deleted the database and don't need future backups, then in the Recovery Services vault, select Stop backup with Retain Backup Data or Delete Backup Data. 有关详细信息,请参阅管理和监视已备份的 SQL Server 数据库For more information, see Manage and monitor backed-up SQL Server databases.

    UserErrorSQLLSNValidationFailureUserErrorSQLLSNValidationFailure

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    日志链已中断。Log chain is broken. 数据库或 VM 是通过其他备份解决方案备份的,该解决方案截断了日志链。The database or the VM is backed up through another backup solution, which truncates the log chain.
    • 检查是否正在使用其他备份解决方案或脚本。Check if another backup solution or script is in use. 如果是,请停止其他备份解决方案。If so, stop the other backup solution.
    • 如果备份是临时日志备份,请触发完整备份来启动新的日志链。If the backup was an ad hoc log backup, trigger a full backup to start a new log chain. 对于计划的日志备份,不需要执行任何操作,因为 Azure 备份服务会自动触发完整备份来解决此问题。For scheduled log backups, no action is needed because the Azure Backup service will automatically trigger a full backup to fix this issue.

    UserErrorOpeningSQLConnectionUserErrorOpeningSQLConnection

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    Azure 备份无法连接到 SQL 实例。Azure Backup is not able to connect to the SQL instance. Azure 备份无法连接到 SQL Server 实例。Azure Backup can't connect to the SQL Server instance. 使用 Azure 门户上错误菜单中的“其他详细信息”缩小根本原因的范围。Use the additional details on the Azure portal error menu to narrow down the root causes. 请参阅 SQL 备份故障排除来解决错误。Refer to SQL backup troubleshooting to fix the error.

    UserErrorParentFullBackupMissingUserErrorParentFullBackupMissing

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    此数据源缺少首次完整备份。First full backup is missing for this data source. 数据库缺少首次完整备份。Full backup is missing for the database. 日志备份和差异备份基于完整备份,因此,在触发差异备份或日志备份之前,必须先创建完整备份。Log and differential backups are parents to a full backup, so be sure to take full backups before triggering differential or log backups. 触发临时完整备份。Trigger an ad hoc full backup.

    UserErrorBackupFailedAsTransactionLogIsFullUserErrorBackupFailedAsTransactionLogIsFull

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    由于数据源的事务日志已满,无法创建备份。Cannot take backup as transaction log for the data source is full. 数据库事务日志空间已满。The database transactional log space is full. 若要解决此问题,请参阅 SQL Server 文档To fix this issue, refer to the SQL Server documentation.

    UserErrorCannotRestoreExistingDBWithoutForceOverwriteUserErrorCannotRestoreExistingDBWithoutForceOverwrite

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    目标位置已存在同名的数据库Database with same name already exists at the target location 目标还原位置已存在同名的数据库。The target restore destination already has a database with the same name.
    • 更改目标数据库名称。Change the target database name.
    • 或在还原页中使用强制覆盖选项。Or, use the force overwrite option on the restore page.

    UserErrorRestoreFailedDatabaseCannotBeOfflinedUserErrorRestoreFailedDatabaseCannotBeOfflined

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    由于无法将数据库脱机,还原失败。Restore failed as the database could not be brought offline. 执行还原时,需将目标数据库脱机。While you're doing a restore, the target database needs to be brought offline. Azure 备份无法将此数据脱机。Azure Backup can't bring this data offline. 使用 Azure 门户上错误菜单中的“其他详细信息”缩小根本原因的范围。Use the additional details on the Azure portal error menu to narrow down the root causes. 有关详细信息,请参阅 SQL Server 文档For more information, see the SQL Server documentation.

    UserErrorCannotFindServerCertificateWithThumbprintUserErrorCannotFindServerCertificateWithThumbprint

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    在目标上找不到包含指纹的服务器证书。Cannot find the server certificate with thumbprint on the target. 目标实例上的 master 数据库没有有效的加密指纹。The master database on the destination instance doesn't have a valid encryption thumbprint. 将源实例上使用的有效证书指纹导入目标实例。Import the valid certificate thumbprint used on the source instance, to the target instance.

    UserErrorRestoreNotPossibleBecauseLogBackupContainsBulkLoggedChangesUserErrorRestoreNotPossibleBecauseLogBackupContainsBulkLoggedChanges

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    用于恢复的日志备份包含批量记录的更改。The log backup used for recovery contains bulk-logged changes. 它不能用来按照 SQL 准则随时停止。It cannot be used to stop at an arbitrary point in time as per the SQL guidelines. 当数据库处于批量记录恢复模式时,批量记录的事务与下一日志事务之间的数据将无法恢复。When a database is in bulk-logged recovery mode, the data between a bulk-logged transaction and the next log transaction can't be recovered. 请选择要恢复到的另一时间点。Choose a different point in time for recovery. 了解详细信息Learn more.

    FabricSvcBackupPreferenceCheckFailedUserErrorFabricSvcBackupPreferenceCheckFailedUserError

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    由于可用性组的某些节点未注册,无法满足 SQL Always On 可用性组的备份首选项。Backup preference for SQL Always On Availability Group cannot be met as some nodes of the Availability Group are not registered. 执行备份所需的节点未注册或不可访问。Nodes required to perform backups are not registered or are unreachable.
    • 确保对此数据库执行备份所需的所有节点已注册且正常,然后重试操作。Ensure that all the nodes required to perform backups of this database are registered and healthy, and then retry the operation.
    • 更改 SQL Server Always On 可用性组的备份优先顺序。Change the backup preference for the SQL Server Always On availability group.

    VMNotInRunningStateUserErrorVMNotInRunningStateUserError

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    SQL Server VM 已关闭,或者无法让 Azure 备份服务访问。SQL server VM is either shutdown and not accessible to Azure Backup service. VM 已关闭。The VM is shut down. 确保 SQL Server 实例正在运行。Ensure that the SQL Server instance is running.

    GuestAgentStatusUnavailableUserErrorGuestAgentStatusUnavailableUserError

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    Azure 备份服务使用 Azure VM 来宾代理执行备份,但来宾代理在目标服务器上不可用。Azure Backup service uses Azure VM guest agent for doing backup but guest agent is not available on the target server. 来宾代理未启用或不正常。The guest agent is not enabled or is unhealthy. 手动安装 VM 来宾代理Install the VM guest agent manually.

    AutoProtectionCancelledOrNotValidAutoProtectionCancelledOrNotValid

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    自动保护意向被删除或不再有效。Auto-protection Intent was either removed or is no more valid. 在 SQL Server 实例上启用自动保护时,将为该实例中的所有数据库运行“配置备份”作业。 When you enable auto-protection on a SQL Server instance, Configure Backup jobs run for all the databases in that instance. 如果在作业运行时禁用自动保护,则会使用此错误代码取消正在进行的作业。If you disable auto-protection while the jobs are running, then the In-Progress jobs are canceled with this error code. 重新启用自动保护可帮助保护所有剩余的数据库。Enable auto-protection once again to help protect all the remaining databases.

    CloudDosAbsoluteLimitReachedCloudDosAbsoluteLimitReached

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    操作已被阻止,因为你已达到 24 小时内允许的操作数量限制。Operation is blocked as you have reached the limit on number of operations permitted in 24 hours. 达到 24 小时内允许的最大操作数量限制后,会出现此错误。When you have reached the maximum permissible limit for an operation in a span of 24 hours, this error comes.
    例如:如果已达到每日可触发的配置备份作业数限制,而你尝试针对新项配置备份,则会出现此错误。For example: If you have hit the limit for the number of configure backup jobs that can be triggered per day, and you try to configure backup on a new item, you will see this error.
    通常,在 24 小时后重试操作即可解决此问题。Typically, retrying the operation after 24 hours resolves this issue. 但是,如果问题持续出现,可以联系 Microsoft 支持人员获得帮助。However, if the issue persists, you can contact Microsoft support for help.

    CloudDosAbsoluteLimitReachedWithRetryCloudDosAbsoluteLimitReachedWithRetry

    错误消息Error message 可能的原因Possible causes 建议的操作Recommended action
    操作被阻止,因为保管库已达到 24 小时内允许的最大此类操作数量限制。Operation is blocked as the vault has reached its maximum limit for such operations permitted in a span of 24 hours. 达到 24 小时内允许的最大操作数量限制后,会出现此错误。When you have reached the maximum permissible limit for an operation in a span of 24 hours, this error comes. 此错误通常发生在执行大规模操作(例如修改策略或自动保护)时。This error usually comes in case of at-scale operations such as modify policy or auto-protection. 与 CloudDosAbsoluteLimitReached 不同,没有太多的办法可以消除此状态,实际上,Azure 备份服务将在内部针对所有有问题的项重试操作。Unlike in the case of CloudDosAbsoluteLimitReached, there is not much you can do to resolve this state, in fact, Azure Backup service will retry the operations internally for all the items in question.
    例如:如果使用某个策略保护了大量的数据源,而你尝试修改该策略,则会针对每个受保护项触发配置保护作业,因此有时可能会达到每日允许的最大此类操作数量限制。For example: If you have a large number of datasources protected with a policy and you try to modify that policy, it will trigger configure protection jobs for each of the protected items and sometimes may hit the maximum limit permissible for such operations per day.
    Azure 备份服务会在 24 小时后自动重试此操作。Azure Backup service will automatically retry this operation after 24 hours.

    重新注册失败Re-registration failures

    在触发重新注册操作之前,请检查是否存在以下一种或多种症状:Check for one or more of the following symptoms before you trigger the re-register operation:

    • 所有操作(例如备份、还原和配置备份)在 VM 失败,并出现以下错误代码之一:WorkloadExtensionNotReachableUserErrorWorkloadExtensionNotInstalledWorkloadExtensionNotPresentWorkloadExtensionDidntDequeueMsgAll operations (such as backup, restore, and configure backup) are failing on the VM with one of the following error codes: WorkloadExtensionNotReachable, UserErrorWorkloadExtensionNotInstalled, WorkloadExtensionNotPresent, WorkloadExtensionDidntDequeueMsg.

    • 备份项的“备份状态”区域显示“无法访问”。 The Backup Status area for the backup item is showing Not reachable. 排除可能导致相同状态的所有其他原因:Rule out all the other causes that might result in the same status:

      • 缺少在 VM 上执行备份相关操作的权限Lack of permission to perform backup-related operations on the VM
      • VM 已关闭,因此备份无法进行Shutdown of the VM, so backups can’t take place
      • 网络问题Network issues

      重新注册 VM 时出现“无法访问”状态

    • 使用 Always On 可用性组时,更改备份优先顺序或完成故障转移后备份开始失败。In the case of an Always On availability group, the backups started failing after you changed the backup preference or after a failover.

    这些症状可能是以下一种或多种原因造成的:These symptoms may arise for one or more of the following reasons:

    • 从门户中删除或卸载了某个扩展。An extension was deleted or uninstalled from the portal.
    • 从该 VM 的“控制面板”中的“卸载或更改程序”下卸载了某个扩展。 An extension was uninstalled from Control Panel on the VM under Uninstall or Change a Program.
    • 已通过就地磁盘还原将该 VM 还原到某个时间点。The VM was restored back in time through in-place disk restore.
    • 该 VM 已关闭较长时间,因此其上的扩展配置已过期。The VM was shut down for an extended period, so the extension configuration on it expired.
    • 删除了该 VM,并在该 VM 所在的同一资源组中创建了同名的另一个 VM。The VM was deleted, and another VM was created with the same name and in the same resource group as the deleted VM.
    • 某个可用性组节点未收到完整的备份配置。One of the availability group nodes didn't receive the complete backup configuration. 将可用性组注册到保管库或添加新节点时,可能会发生这种情况。This can happen when the availability group is registered to the vault or when a new node is added.

    对于上述场景,我们建议在 VM 上触发重新注册操作。In the preceding scenarios, we recommend that you trigger a re-register operation on the VM. 目前,此选项只能通过 PowerShell 使用。For now, this option is available only through PowerShell.

    文件大小限制Size limit for files

    文件的总字符串大小不仅取决于文件数量,而且还取决于文件的名称和路径。The total string size of files depends not only on the number of files but also on their names and paths. 对于每个数据库文件,需获取逻辑文件名和物理路径。For each database file, get the logical file name and physical path. 可以使用以下 SQL 查询:You can use this SQL query:

    SELECT mf.name AS LogicalName, Physical_Name AS Location FROM sys.master_files mf
                   INNER JOIN sys.databases db ON db.database_id = mf.database_id
                   WHERE db.name = N'<Database Name>'"
    

    现在,按以下格式排列文件:Now arrange them in the following format:

    [{"path":"<Location>","logicalName":"<LogicalName>","isDir":false},{"path":"<Location>","logicalName":"<LogicalName>","isDir":false}]}
    

    下面是一个示例:Here's an example:

    [{"path":"F:\\Data\\TestDB12.mdf","logicalName":"TestDB12","isDir":false},{"path":"F:\\Log\\TestDB12_log.ldf","logicalName":"TestDB12_log","isDir":false}]}
    

    如果内容的字符串大小超过 20,000 字节,将以不同的方式存储数据库文件。If the string size of the content exceeds 20,000 bytes, the database files are stored differently. 在恢复期间,无法设置要还原到的目标文件路径。During recovery, you won't be able to set the target file path for restore. 文件将还原到 SQL Server 提供的默认 SQL 路径。The files will be restored to the default SQL path provided by SQL Server.

    替代默认的目标还原文件路径Override the default target restore file path

    在执行还原操作期间,可以通过将包含数据库文件映射的 JSON 文件放入目标还原路径,来替代目标还原文件路径。You can override the target restore file path during the restore operation by placing a JSON file that contains the mapping of the database file to the target restore path. 创建 database_name.json 文件,并将其放入 C:\Program Files\Azure Backup\bin\plugins\SQL 位置。Create a database_name.json file and place it in the location C:\Program Files\Azure Workload Backup\bin\plugins\SQL.

    该文件的内容应采用以下格式:The content of the file should be in this format:

    [
      {
        "Path": "<Restore_Path>",
        "LogicalName": "<LogicalName>",
        "IsDir": "false"
      },
      {
        "Path": "<Restore_Path>",
        "LogicalName": "LogicalName",
        "IsDir": "false"
      },  
    ]
    

    下面是一个示例:Here's an example:

    [
      {
       "Path": "F:\\Data\\testdb2_1546408741449456.mdf",
       "LogicalName": "testdb7",
       "IsDir": "false"
      },
      {
        "Path": "F:\\Log\\testdb2_log_1546408741449456.ldf",
        "LogicalName": "testdb7_log",
        "IsDir": "false"
      },  
    ]
    

    在上述内容中,可以使用以下 SQL 查询获取数据库文件的逻辑名称:In the preceding content, you can get the logical name of the database file by using the following SQL query:

    SELECT mf.name AS LogicalName FROM sys.master_files mf
                    INNER JOIN sys.databases db ON db.database_id = mf.database_id
                    WHERE db.name = N'<Database Name>'"
    

    应在触发还原操作之前放置此文件。This file should be placed before you trigger the restore operation.

    后续步骤Next steps

    有 SQL Server VM 的 Azure 备份(公共预览版)的详细信息,请参阅 SQL VM 的 Azure 备份For more information about Azure Backup for SQL Server VMs (public preview), see Azure Backup for SQL VMs.