Preparar um banco de dados secundário manualmente para um Grupo de Disponibilidade AlwaysOnPrepare a secondary database for an Always On availability group

APLICA-SE A: simSQL Server nãoBanco de Dados SQL do Azure nãoSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Este tópico descreve como preparar um banco de dados para um grupo de disponibilidade AlwaysOn no SQL ServerSQL Server usando o SQL Server Management StudioSQL Server Management Studio, o Transact-SQLTransact-SQL ou o PowerShell.This topic describes how to prepare a database for an Always On availability group in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell. A preparação de um banco de dados exige duas etapas:Preparing a database requires two steps:

  1. Restaurar um backup de banco de dados recente do banco de dados primário e os backups de log posteriores em cada instância de servidor que hospeda a réplica secundária, usando RESTORE WITH NORECOVERYRestore a recent database backup of the primary database and subsequent log backups onto each server instance that hosts the secondary replica, using RESTORE WITH NORECOVERY
  2. Ingressar o banco de dados restaurado no grupo de disponibilidade.Join the restored database to the availability group.

Dica

Se você tiver uma configuração de envio de logs existente, talvez consiga converter o banco de dados primário de envio de logs junto com um ou mais de seus bancos de dados secundários em uma réplica primária do grupo de disponibilidade e em uma ou mais réplicas secundárias.If you have an existing log shipping configuration, you might be able to convert the log shipping primary database along with one or more of its secondary databases to an availability group primary replica and one or more secondary replicas. Para obter mais informações, consulte Pré-requisitos para a migração do envio de logs para Grupos de Disponibilidade AlwaysOn (SQL Server).For more information, see Prerequisites for migrating from log Shipping to Always On Availability Groups (SQL Server).

Pré-requisitos e restriçõesPrerequisites and restrictions

  • Verifique se o sistema onde você planeja colocar o banco de dados possui um disco com espaço suficiente para os bancos de dados secundários.Make sure that the system where you plan to place database possesses a disk drive with sufficient space for the secondary databases.

  • O nome do banco de dados secundário deve ser igual ao nome do banco de dados primário.The name of the secondary database must be the same as the name of the primary database.

  • Use RESTORE WITH NORECOVERY para cada operação de restauração.Use RESTORE WITH NORECOVERY for every restore operation.

  • Se o banco de dados secundário precisar residir em um caminho de arquivo diferente (inclusive a letra da unidade) do banco de dados primário, o comando de restauração também deve usar a opção WITH MOVE para cada um dos arquivos de banco de dados para especificar o caminho do banco de dados secundário.If the secondary database needs to reside on a different file path (including the drive letter) than the primary database, the restore command must also use the WITH MOVE option for each of the database files to specify them to the path of the secondary database.

  • Se restaurar o grupo de arquivos de banco de dados pelo grupo de arquivos, restaure todo o banco de dados.If you restore the database filegroup by filegroup, be sure to restore the whole database.

  • Depois de restaurar o banco de dados, você deve restaurar (WITH NORECOVERY) cada backup de log criado desde o último backup de dados restaurado.After restoring the database, you must restore (WITH NORECOVERY) every log backup created since the last restored data backup.

RecomendaçõesRecommendations

  • Em instâncias autônomas do SQL ServerSQL Server, é recomendável que, se possível, o caminho do arquivo (incluindo a letra da unidade) de um determinado banco de dados secundário seja idêntico ao caminho do banco de dados primário correspondente.On stand-alone instances of SQL ServerSQL Server, we recommend that, if possible, the file path (including the drive letter) of a given secondary database be identical to the path of the corresponding primary database. Isso ocorre porque, se você mover os arquivos de banco de dados ao criar um banco de dados secundário, uma operação de adição de arquivo posterior poderá apresentar falha no banco de dados secundário e fazer com que o banco de dados secundário seja suspenso.This is because if you move the database files when creating a secondary database, a later add-file operation might fail on the secondary database and cause the secondary database to be suspended.

  • Antes de preparar seus bancos de dados secundários, é altamente recomendável suspender os backups de log agendados nos bancos de dados no grupo de disponibilidade até que a inicialização das réplicas secundárias seja concluída.Before preparing your secondary databases, we strongly recommend that you suspend scheduled log backups on the databases in the availability group until the initialization of secondary replicas has completed.

SegurançaSecurity

Quando é feito backup de um banco de dados, a propriedade TRUSTWORTHY do banco de dados é definida como OFF.When a database is backed up, the TRUSTWORTHY database property is set to OFF. Portanto, em um banco de dados recém-restaurado, TRUSTWORTHY sempre será OFF.Therefore, TRUSTWORTHY is always OFF on a newly restored database.

PermissõesPermissions

As permissões BACKUP DATABASE e BACKUP LOG usam como padrão os membros da função de servidor fixa sysadmin e as funções de banco de dados fixas db_owner e db_backupoperator .BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. Para obter mais informações, veja BACKUP (Transact-SQL).For more information, see BACKUP (Transact-SQL).

Quando o banco de dados que está sendo restaurado não existir na instância do servidor, a instrução RESTORE exigirá as permissões CREATE DATABASE.When the database being restored does not exist on the server instance, the RESTORE statement requires CREATE DATABASE permissions. Para obter mais informações, veja RESTORE (Transact-SQL).For more information, see RESTORE (Transact-SQL).

Usar o SQL Server Management StudioUse SQL Server Management Studio

Observação

Se os caminhos de arquivos de backup e restauração forem idênticos entre a instância de servidor que hospeda a réplica primária e todas as instâncias que hospedam uma réplica secundária, você deverá conseguir criar bancos de dados secundários com o Assistente de Novo Grupo de Disponibilidade, o Assistente para Adicionar Réplica a um Grupo de Disponibilidade ou o Assistente para Adicionar Banco de Dados a um Grupo de Disponibilidade.If the backup and restore file paths are identical between the server instance that hosts the primary replica and every instance that hosts a secondary replica, you should be able create secondary replica databases by with New Availability Group Wizard, Add Replica to Availability Group Wizard, or Add Database to Availability Group Wizard.

Para preparar um banco de dados secundárioTo prepare a secondary database

  1. A menos que você já tenha um backup recente do banco de dados primário, crie um novo backup completo ou diferencial do banco de dados.Unless you already have a recent database backup of the primary database, create a new full or differential database backup. Como prática recomendada, coloque esse backup e qualquer backup de log subsequente no compartilhamento de rede recomendado.As a best practice, place this backup and any subsequent log backups onto the recommended network share.

  2. Crie pelo menos um novo backup de log do banco de dados primário.Create at least one new log backup of the primary database.

Observação

Um backup de log de transações poderá não ser necessário se um backup de log de transações não tiver sido capturado anteriormente no banco de dados na réplica primária.A transaction log backup may not be required if a transaction log backup has not been previously captured on the database in the primary replica. A Microsoft recomenda fazer um backup de log de transações sempre que um novo banco de dados é ingressado no grupo de disponibilidade.Microsoft recommends taking a transaction log backup each time a new database is joined to the availability group.

  1. Na instância do servidor que hospeda a réplica secundária, restaure o backup completo do banco de dados primário (e opcionalmente um backup diferencial) seguido por quaisquer backups de log subsequentes.On the server instance that hosts the secondary replica, restore the full database backup of the primary database (and optionally a differential backup) followed by any subsequent log backups.

    Na página Opções de RESTORE DATABASE, selecione Deixar o banco de dados não operacional e não reverter as transações não confirmadas. Os logs de transações adicionais podem ser restaurados. (RESTAURAR COM NORECOVERY) .On the RESTORE DATABASE Options page, select Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY).

    Se os caminhos de arquivo dos bancos de dados primário e secundário forem diferentes, por exemplo, se o banco de dados primário estiver na unidade 'F:', mas a instância do servidor que hospeda a réplica secundária não tiver uma unidade F:, inclua a opção MOVE na cláusula WITH.If the file paths of the primary database and the secondary database differ, for example, if the primary database is on drive 'F:' but the server instance that hosts the secondary replica lacks an F: drive, include the MOVE option in your WITH clause.

  2. Para concluir a configuração do banco de dados secundário, você precisa unir o banco de dados secundário ao grupo de disponibilidade.To complete configuration of the secondary database, you need to join the secondary database to the availability group. Para obter mais informações, veja Unir um banco de dados secundário a um grupo de disponibilidade (SQL Server).For more information, Join a Secondary Database to an Availability Group (SQL Server).

Observação

Para obter informações sobre como executar estas operações de backup e restauração, veja Tarefas de backup e restauração relacionadas, mais adiante nesta seção.For information about how to perform these backup and restore operations, see Related Backup and Restore Tasks, later in this section.

Tarefas relacionadas a backup e restauraçãoRelated Backup and Restore Tasks

Para criar um backup de banco de dadosTo create a database backup

Para criar um backup do logTo create a log backup

Para restaurar backupsTo restore backups

Usando o Transact-SQLUsing Transact-SQL

Para preparar um banco de dados secundárioTo prepare a secondary database

Observação

Para obter um exemplo desse procedimento, veja Exemplo (Transact-SQL), acima neste tópico.For an example of this procedure, see Example (Transact-SQL), earlier in this topic.

  1. A menos que você tenha um backup completo recente do banco de dados primário, conecte-se à instância do servidor que hospeda a réplica primária e crie um backup completo do banco de dados.Unless you have a recent full backup of the primary database, connect to the server instance that hosts the primary replica and create a full database backup. Como prática recomendada, coloque esse backup e qualquer backup de log subsequente no compartilhamento de rede recomendado.As a best practice, place this backup and any subsequent log backups onto the recommended network share.

  2. Na instância do servidor que hospeda a réplica secundária, restaure o backup completo do banco de dados primário (e opcionalmente um backup diferencial) seguido por todos os backups de log subsequentes.On the server instance that hosts the secondary replica, restore the full database backup of the primary database (and optionally a differential backup) followed by all subsequent log backups. Use WITH NORECOVERY para cada operação de restauração.Use WITH NORECOVERY for every restore operation.

    Se os caminhos de arquivo dos bancos de dados primário e secundário forem diferentes, por exemplo, se o banco de dados primário estiver na unidade 'F:', mas a instância do servidor que hospeda a réplica secundária não tiver uma unidade F:, inclua a opção MOVE na cláusula WITH.If the file paths of the primary database and the secondary database differ, for example, if the primary database is on drive 'F:' but the server instance that hosts the secondary replica lacks an F: drive, include the MOVE option in your WITH clause.

  3. Se quaisquer backups de log tiverem sido executados no banco de dados primário desde o backup de log necessário, será necessário copiá-los na instância do servidor que hospeda a réplica secundária e aplicar cada um desses backups de log ao banco de dados secundário, começando com o mais recente e sempre usando RESTORE WITH NORECOVERY.If any log backups have been taken on the primary database since the required log backup, you must also copy these to the server instance that hosts the secondary replica and apply each of those log backups to the secondary database, starting with the earliest and always using RESTORE WITH NORECOVERY.

    Observação

    Um backup de log não existirá se o banco de dados primário tiver acabado de ser criado, e nenhum backup de log tiver sido executado ainda, ou se o modelo de recuperação tiver acabado de ser alterado de simples para completo.A log backup would not exist if the primary 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. Para concluir a configuração do banco de dados secundário, você precisa unir o banco de dados secundário ao grupo de disponibilidade.To complete configuration of the secondary database, you need to join the secondary database to the availability group. Para obter mais informações, veja Unir um banco de dados secundário a um grupo de disponibilidade (SQL Server).For more information, Join a Secondary Database to an Availability Group (SQL Server).

Observação

Para obter informações sobre como executar estas operações de backup e restauração, veja Tarefas de backup e restauração relacionadas, mais adiante neste tópico.For information about how to perform these backup and restore operations, see Related Backup and Restore Tasks, later in this topic.

Exemplo de Transact-SQLTransact-SQL Example

O exemplo a seguir prepara um banco de dados secundário.The following example prepares a secondary database. Esse exemplo usa o banco de dados de exemplo do AdventureWorks2012AdventureWorks2012 que, por padrão, usa o modelo de recuperação simples.This example uses the AdventureWorks2012AdventureWorks2012 sample database, which uses the simple recovery model by default.

  1. Para usar o banco de dados AdventureWorks2012AdventureWorks2012 , modifique-o para usar o modelo de recuperação completa:To use the AdventureWorks2012AdventureWorks2012 database, modify it to use the full recovery model:

    USE master;  
    GO  
    ALTER DATABASE MyDB1   
    SET RECOVERY FULL;  
    GO  
    
  2. Depois de modificar o modelo de recuperação do banco de dados de SIMPLE para FULL, crie um backup completo, que pode ser usado para criar o banco de dados secundário.After modifying the recovery model of the database from SIMPLE to FULL, create a full backup, which can be used to create the secondary database. Como o modelo de recuperação acabou de ser alterado, a opção WITH FORMAT estará especificada para criar um novo conjunto de mídias.Because the recovery model has just been changed, the WITH FORMAT option is specified to create a new media set. Isso é útil para separar os backups sob o modelo de recuperação completa de qualquer backup anterior feito sob o modelo de recuperação simples.This is useful to separate the backups under the full recovery model from any previous backups made under the simple recovery model. Para a finalidade deste exemplo, o arquivo de backup (C:\AdventureWorks2012AdventureWorks2012.bak) será criado na mesma unidade que o banco de dados.For the purpose of this example, the backup file (C:\AdventureWorks2012AdventureWorks2012.bak) is created on the same drive as the database.

    Observação

    Em um banco de dados de produção, você deve sempre fazer backup em um dispositivo separado.For a production database, you should always back up to a separate device.

    Na instância do servidor que hospeda a réplica primária (INSTANCE01), crie um backup completo do banco de dados primário da seguinte maneira:On the server instance that hosts the primary replica (INSTANCE01), create a full backup of the primary database as follows:

    BACKUP DATABASE MyDB1   
        TO DISK = 'C:\MyDB1.bak'   
        WITH FORMAT  
    GO  
    
  3. Copie o backup completo na instância do servidor que hospeda a réplica secundária.Copy the full backup to the server instance that hosts the secondary replica.

  4. Restaure o backup completo usando RESTORE WITH NORECOVERY na instância do servidor que hospeda a réplica secundária.Restore the full backup, using RESTORE WITH NORECOVERY, onto the server instance that hosts the secondary replica. O comando de restauração depende de se os caminhos dos bancos de dados primário e secundário são idênticos.The restore command depends on whether the paths of primary and secondary databases are identical.

    • Se os caminhos forem idênticos:If the paths are identical:

      No computador que hospeda a réplica secundária, restaure o backup completo da seguinte maneira:On the computer that hosts the secondary replica, restore the full backup as follows:

      RESTORE DATABASE MyDB1   
          FROM DISK = 'C:\MyDB1.bak'   
          WITH NORECOVERY  
      GO  
      
    • Se os caminhos forem diferentes:If the paths differ:

      Se o caminho do banco de dados secundário for diferente do caminho do banco de dados primário (por exemplo, se as letras das unidades forem diferentes), a criação do banco de dados secundário exigirá que a operação de restauração inclua uma cláusula MOVE.If the path of the secondary database differs from the path of the primary database (for instance, their drive letters differ), creating the secondary database requires that the restore operation include a MOVE clause.

      Importante

      Se os nomes dos caminhos dos bancos de dados primário e secundário forem diferentes, não será possível adicionar um arquivo.If the path names of the primary and secondary databases differ, you cannot add a file. Isso acontece porque, ao receber o log para a operação de adição de arquivo, a instância do servidor da réplica secundária tenta colocar o novo arquivo no mesmo caminho usado pelo banco de dados primário.This is because on receiving the log for the add file operation, the server instance of the secondary replica attempts to place the new file in the same path as used by the primary database.

      Por exemplo, o comando a seguir restaura um backup de um banco de dados primário que reside no diretório de dados da instância padrão do SQL Server 2017SQL Server 2017, C:\Arquivos de Programas\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA.For example, the following command restores a backup of a primary database that resides in the data directory of the default instance of SQL Server 2017SQL Server 2017, C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA. A operação de restauração do banco de dados deve mover o banco de dados para o diretório de dados de uma instância remota do SQL Server 2017SQL Server 2017 denominada (Always On1), que hospeda a réplica secundária em outro nó de cluster.The restore database operation must move the database to the data directory of a remote instance of SQL Server 2017SQL Server 2017 named (Always On1), which hosts the secondary replica on another cluster node. Lá, os arquivos de dados e de log são restaurados para o diretório C:\Arquivos de Programas\Microsoft SQL Server\MSSQL13.Always On1\MSSQL\DATA .There, the data and log files are restored to the C:\Program Files\Microsoft SQL Server\MSSQL13.Always On1\MSSQL\DATA directory . O operação de restauração usa WITH NORECOVERY, para deixar o banco de dados secundário no banco de dados de restauração.The restore operation uses WITH NORECOVERY, to leave the secondary database in the restoring database.

      RESTORE DATABASE MyDB1  
        FROM DISK='C:\MyDB1.bak'  
       WITH NORECOVERY,   
          MOVE 'MyDB1_Data' TO   
           'C:\Program Files\Microsoft SQL Server\MSSQL13.Always On1\MSSQL\DATA\MyDB1_Data.mdf',   
          MOVE 'MyDB1_Log' TO  
           'C:\Program Files\Microsoft SQL Server\MSSQL13.Always On1\MSSQL\DATA\MyDB1_Data.ldf';  
      GO  
      
  5. Depois que você restaura o backup completo, será necessário criar um backup de log no banco de dados primário.After you restore the full backup, you must create a log backup on the primary database. Por exemplo, a seguinte instrução Transact-SQLTransact-SQL faz backup do log em um arquivo de backup chamado E:\MyDB1_log.trn:For example, the following Transact-SQLTransact-SQL statement backs up the log to the a backup file named E:\MyDB1_log.trn:

    BACKUP LOG MyDB1   
      TO DISK = 'E:\MyDB1_log.trn'   
    GO  
    
  6. Para poder unir o banco de dados com a réplica secundária, é necessário aplicar o backup de log exigido (e qualquer backup de log subsequente).Before you can join the database to the secondary replica, you must apply the required log backup (and any subsequent log backups).

    Por exemplo, a seguinte instrução Transact-SQLTransact-SQL restaura o primeiro log de C:\MyDB1.trn:For example, the following Transact-SQLTransact-SQL statement restores the first log from C:\MyDB1.trn:

    RESTORE LOG MyDB1   
      FROM DISK = 'E:\MyDB1_log.trn'   
        WITH FILE=1, NORECOVERY  
    GO  
    
  7. Se qualquer backup de log adicional ocorrer antes da junção do banco de dados com a réplica secundária, você também deverá restaurar todos esses backups de log, em sequência, na instância do servidor que hospeda a réplica secundária usando RESTORE WITH NORECOVERY.If any additional log backups occur before the database joins the secondary replica, you must also restore all of those log backups, in sequence, to the server instance that hosts the secondary replica using RESTORE WITH NORECOVERY.

    Por exemplo, a seguinte instrução Transact-SQLTransact-SQL restaura dois logs adicionais de E:\MyDB1_log.trn:For example, the following Transact-SQLTransact-SQL statement restores two additional logs from E:\MyDB1_log.trn:

    RESTORE LOG MyDB1   
      FROM DISK = 'E:\MyDB1_log.trn'   
        WITH FILE=2, NORECOVERY  
    GO  
    RESTORE LOG MyDB1   
      FROM DISK = 'E:\MyDB1_log.trn'   
        WITH FILE=3, NORECOVERY  
    GO  
    

Usando o PowerShellUsing PowerShell

Para preparar um banco de dados secundárioTo prepare a secondary database

  1. Se você precisar criar um backup recente do banco de dados primário, altere o diretório (cd) para a instância de servidor que hospeda a réplica primária.If you need to create a recent backup of the primary database, change directory (cd) to the server instance that hosts the primary replica.

  2. Use o cmdlet Backup-SqlDatabase para criar cada um dos backups.Use the Backup-SqlDatabase cmdlet to create each of the backups.

  3. Altere o diretório (cd) para a instância de servidor que hospeda a réplica secundária.Change directory (cd) to the server instance that hosts the secondary replica.

  4. Para restaurar os backups do banco de dados e do log de cada banco de dados primário, use o cmdlet restore-SqlDatabase , especificando o parâmetro de restauração NoRecovery .To restore the database and log backups of each primary database, use the restore-SqlDatabase cmdlet, specifying the NoRecovery restore parameter. Se os caminhos dos arquivos forem diferentes nos computadores que hospedam a réplica primária e a réplica secundária de destino, use também o parâmetro de restauração RelocateFile .If the file paths differ between the computers that host the primary replica and the target secondary replica, also use the RelocateFile restore parameter.

    Observação

    Para exibir a sintaxe de um cmdlet, use o cmdlet Get-Help no ambiente do SQL ServerSQL Server PowerShell.To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL ServerSQL Server PowerShell environment. Para obter mais informações, consulte Get Help SQL Server PowerShell.For more information, see Get Help SQL Server PowerShell.

  5. Para concluir a configuração do banco de dados secundário, você precisa uni-lo ao grupo de disponibilidade.To complete configuration of the secondary database, you need to join it to the availability group. Para obter mais informações, veja Unir um banco de dados secundário a um grupo de disponibilidade (SQL Server).For more information, Join a Secondary Database to an Availability Group (SQL Server).

Para configurar e usar o provedor do SQL Server PowerShellTo set up and use the SQL Server PowerShell provider

Script e comando de backup e restauração de exemploSample backup and restore script and command

Os comandos PowerShell a seguir fazem backup de um backup de banco de dados completo e do log de transações em um compartilhamento de rede e restaura esses backups a partir desse compartilhamento.The following PowerShell commands back up a full database backup and transaction log to a network share and restore those backups from that share. Este exemplo supõe que o caminho do arquivo para o qual o banco de dados é restaurado é igual ao caminho do arquivo no qual foi feito o backup do banco de dados.This example assumes that the file path to which the database is restored is the same as the file path on which the database was backed up.

# Create database backup  
Backup-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.bak" -ServerInstance "SourceMachine\Instance"  
# Create log backup  
Backup-SqlDatabase -Database "MyDB1" -BackupAction "Log" -BackupFile "\\share\backups\MyDB1.trn" -ServerInstance "SourceMachine\Instance"  
# Restore database backup   
Restore-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.bak" -NoRecovery -ServerInstance "DestinationMachine\Instance"  
# Restore log backup   
Restore-SqlDatabase -Database "MyDB1" -BackupFile "\\share\backups\MyDB1.trn" -RestoreAction "Log" -NoRecovery -ServerInstance "DestinationMachine\Instance"  
  

Próximas etapasNext steps

Para concluir a configuração do banco de dados secundário, una o banco de dados recém-restaurado ao grupo de disponibilidade.To complete configuration of the secondary database, join the newly restored database to the availability group. Para obter mais informações, consulte Unir um banco de dados secundário a um grupo de disponibilidade (SQL Server).For more information, see Join a Secondary Database to an Availability Group (SQL Server).

Confira tambémSee also

Visão geral dos grupos de disponibilidade AlwaysOn (SQL Server) Overview of Always On Availability Groups (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
Argumentos de RESTORE (Transact-SQL) RESTORE Arguments (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
Solução de problemas de uma operação de adição de arquivos com falha (Grupos de disponibilidade de AlwaysOn)Troubleshoot a Failed Add-File Operation (Always On Availability Groups)