Preparare manualmente un database per un gruppo di disponibilità (SQL Server)Manually prepare a database for an Availability Group (SQL Server)

Questo argomento illustra come preparare un database per un gruppo di disponibilità Always On in SQL ServerSQL Server usando SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL 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. La preparazione di un database richiede due passaggi:Preparing a database requires two steps:

  1. Ripristinare un back recente del database primario e backup di log successivi in ogni istanza del server in cui viene ospitata la replica secondaria, 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. Creare un join del database ripristinato al gruppo di disponibilità.Join the restored database to the availability group.
Suggerimento

Se si dispone di una configurazione per il log shipping esistente, è possibile convertire il database primario per il log shipping insieme a uno o più dei relativi database secondari in una replica primaria del gruppo di disponibilità e una o più delle relative repliche secondarie.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. Per altre informazioni, vedere Prerequisiti per la migrazione dal log shipping ai gruppi di disponibilità AlwaysOn (SQL Server).For more information, see Prerequisites for migrating from log Shipping to Always On Availability Groups (SQL Server).

Prerequisiti e restrizioni Prerequisites and restrictions

  • Verificare che nel sistema in cui si desidera collocare il database sia presente un'unità disco con spazio sufficiente per i database secondari.Make sure that the system where you plan to place database possesses a disk drive with sufficient space for the secondary databases.

  • Il nome del database secondario deve essere lo stesso del database primario.The name of the secondary database must be the same as the name of the primary database.

  • Utilizzare RESTORE WITH NORECOVERY per ogni operazione di ripristino.Use RESTORE WITH NORECOVERY for every restore operation.

  • Se il database secondario deve risiedere in un percorso di file diverso (inclusa la lettera dell'unità) dal database primario, è inoltre necessario utilizzare l'opzione WITH MOVE nel comando Restore per ognuno dei file di database per specificare il percorso del database secondario.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 si ripristina il database un filegroup alla volta, prestare attenzione a ripristinare l'intero database.If you restore the database filegroup by filegroup, be sure to restore the whole database.

  • Dopo il ripristino del database, è necessario ripristinare (WITH NORECOVERY) ogni backup del log creato dall'ultimo backup dei dati ripristinato.After restoring the database, you must restore (WITH NORECOVERY) every log backup created since the last restored data backup.

Indicazioni Recommendations

  • Nelle istanze autonome di SQL ServerSQL Serverè consigliabile che il percorso del file di un determinato database secondario, inclusa la lettera di unità, sia se possibile identico a quello del database primario corrispondente.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. Se durante la creazione di un database secondario i file del database vengono spostati, infatti, potrebbe essere impossibile aggiungere successivamente file al database secondario senza sospendere il database secondario.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.

  • Prima di preparare i database secondari, si consiglia di sospendere i backup del log pianificati sui database nel gruppo di disponibilità finché non viene completata l'inizializzazione delle repliche secondarie.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.

Sicurezza Security

Quando viene eseguito il backup di un database, la proprietà TRUSTWORTHY del database viene impostata su OFF.When a database is backed up, the TRUSTWORTHY database property is set to OFF. Di conseguenza, la proprietà TRUSTWORTHY è sempre impostata su OFF in un database appena ripristinato.Therefore, TRUSTWORTHY is always OFF on a newly restored database.

Autorizzazioni Permissions

Le autorizzazioni BACKUP DATABASE e BACKUP LOG vengono assegnate per impostazione predefinita ai membri del ruolo predefinito del server sysadmin e dei ruoli predefiniti del database 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. Per altre informazioni, vedere BACKUP (Transact-SQL).For more information, see BACKUP (Transact-SQL).

Se il database da ripristinare non esiste nell'istanza del server, l'istruzione RESTORE richiede autorizzazioni CREATE DATABASE.When the database being restored does not exist on the server instance, the RESTORE statement requires CREATE DATABASE permissions. Per altre informazioni, vedere RESTORE (Transact-SQL).For more information, see RESTORE (Transact-SQL).

Utilizzo di SQL Server Management Studio Use SQL Server Management Studio

Nota

Se i percorsi dei file di backup e ripristino sono identici nell'istanza del server che ospita una replica primaria e in ogni istanza che ospita una replica secondaria, è possibile creare database di replica seocondari usando la Creazione guidata Gruppo di disponibilità, la procedura guidata Aggiungi replica a gruppo di disponibilitào la procedura guidata Aggiungi database a gruppo di disponibilità.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.

Per preparare un database secondarioTo prepare a secondary database

  1. A meno che non si disponga già di un backup recente del database primario, creare un nuovo backup del database completo o differenziale.Unless you already have a recent database backup of the primary database, create a new full or differential database backup. Secondo la procedura consigliata, collocare il backup ed eventuali backup del log successivi nella condivisione di rete consigliata.As a best practice, place this backup and any subsequent log backups onto the recommended network share.

  2. Creare almeno un nuovo backup del log del database primario.Create at least one new log backup of the primary database.

    Nota

    Un backup del log delle transazioni potrebbe non essere necessario se non è stato acquisito in precedenza nel database nella replica primaria.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. È consigliabile effettuare un backup del log delle transazioni ogni volta che un nuovo database viene unito in join al gruppo di disponibilità.Microsoft recommends taking a transaction log backup each time a new database is joined to the availability group.

  3. Nell'istanza del server che ospita la replica secondaria, ripristinare il backup completo del database primario (e facoltativamente un backup differenziale) seguito da eventuali backup del log successivi.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.

    Nella pagina Opzioni di RESTORE DATABASE, selezionare Lascia il database non operativo e non eseguire il rollback delle transazioni di cui non è stato eseguito il commit. I log delle transazioni aggiuntivi possono essere ripristinati. (RESTORE WITH 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 i percorsi dei file del database primario e del database secondario sono diversi, ad esempio se il database primario si trova nell'unità "F:" ma nell'istanza del server che ospita la replica secondaria non è disponibile un'unità "F:", includere l'opzione MOVE nella clausola 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.

  4. Per completare la configurazione del database secondario, è necessario creare un join del database secondario al gruppo di disponibilità.To complete configuration of the secondary database, you need to join the secondary database to the availability group. Per altre informazioni, vedere Creare un join di un database secondario a un gruppo di disponibilità (SQL Server).For more information, Join a Secondary Database to an Availability Group (SQL Server).

Nota

Per informazioni sull'esecuzione di queste operazioni di backup e ripristino, vedere Attività correlate a backup e ripristino, più avanti in questa sezione.For information about how to perform these backup and restore operations, see Related Backup and Restore Tasks, later in this section.

Per creare un backup del databaseTo create a database backup

Utilizzo di Transact-SQL Using Transact-SQL

Per preparare un database secondarioTo prepare a secondary database

Nota

Per un esempio di questa procedura, vedere Esempio (Transact-SQL), più indietro in questo argomento.For an example of this procedure, see Example (Transact-SQL), earlier in this topic.

  1. A meno che si disponga di un backup completo recente del database primario, connettersi all'istanza del server che ospita la replica primaria e creare un backup completo del database.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. Secondo la procedura consigliata, collocare il backup ed eventuali backup del log successivi nella condivisione di rete consigliata.As a best practice, place this backup and any subsequent log backups onto the recommended network share.

  2. Nell'istanza del server che ospita la replica secondaria, ripristinare il backup completo del database primario (e facoltativamente un backup differenziale) seguito da tutti i backup del log successivi.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. Utilizzare WITH NORECOVERY per ogni operazione di ripristino.Use WITH NORECOVERY for every restore operation.

    Se i percorsi dei file del database primario e del database secondario sono diversi, ad esempio se il database primario si trova nell'unità "F:" ma nell'istanza del server che ospita la replica secondaria non è disponibile un'unità "F:", includere l'opzione MOVE nella clausola 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 sono stati eseguiti altri backup del log sul database primario dopo il backup del log richiesto, è inoltre necessario copiarli nell'istanza del server che ospita la replica secondaria e applicare ognuno di questi backup del log al database secondario, a partire dal meno recente e utilizzando sempre 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.

    Nota

    Il backup del log non esiste se il database primario è stato appena creato e non è ancora stato eseguito alcun backup del log oppure se il modello di recupero è stato appena modificato da SIMPLE a FULL.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. Per completare la configurazione del database secondario, è necessario creare un join del database secondario al gruppo di disponibilità.To complete configuration of the secondary database, you need to join the secondary database to the availability group. Per altre informazioni, vedere Creare un join di un database secondario a un gruppo di disponibilità (SQL Server).For more information, Join a Secondary Database to an Availability Group (SQL Server).

Nota

Per informazioni sull'esecuzione di queste operazioni di backup e ripristino, vedere Attività correlate a backup e ripristino, più avanti in questo argomento.For information about how to perform these backup and restore operations, see Related Backup and Restore Tasks, later in this topic.

Esempio Transact-SQL Transact-SQL Example

Nell'esempio seguente viene preparato un database secondario.The following example prepares a secondary database. Nell'esempio viene utilizzato il database di esempio AdventureWorks2012AdventureWorks2012 in cui, per impostazione predefinita, viene utilizzato il modello di recupero con registrazione minima.This example uses the AdventureWorks2012AdventureWorks2012 sample database, which uses the simple recovery model by default.

  1. Per utilizzare il database AdventureWorks2012AdventureWorks2012 , modificarlo in modo da utilizzare il modello di recupero con registrazione 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. Dopo aver modificato il modello di recupero del database da SIMPLE a FULL, creare un backup completo da utilizzare per la creazione del database secondario.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. Dopo la modifica del modello di recupero, è consigliabile selezionare l'opzione WITH FORMAT per creare un nuovo set di supporti.Because the recovery model has just been changed, the WITH FORMAT option is specified to create a new media set. L'operazione risulta utile per separare i backup eseguiti durante l'utilizzo del modello di recupero con registrazione completa dai backup precedenti eseguiti durante l'utilizzo del modello di recupero con registrazione semplice.This is useful to separate the backups under the full recovery model from any previous backups made under the simple recovery model. Ai fini di questo esempio, il file di backup (C:\ AdventureWorks2012AdventureWorks2012.bak) verrà creato nella stessa unità del database.For the purpose of this example, the backup file (C:\ AdventureWorks2012AdventureWorks2012.bak) is created on the same drive as the database.

    Nota

    Nel caso di un database di produzione, è consigliabile eseguire sempre il backup in un dispositivo distinto.For a production database, you should always back up to a separate device.

    Nell'istanza del server che ospita la replica primaria (INSTANCE01), creare un backup completo del database primario, nel modo seguente: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. Copiare il backup completo nell'istanza del server in cui è ospitata la replica secondaria.Copy the full backup to the server instance that hosts the secondary replica.

  4. Ripristinare il backup completo nell'istanza del server che ospita la replica secondaria, utilizzando RESTORE WITH NORECOVERY.Restore the full backup, using RESTORE WITH NORECOVERY, onto the server instance that hosts the secondary replica. Il comando di ripristino dipende dal fatto che i percorsi del database primario e di quelli secondari siano identici.The restore command depends on whether the paths of primary and secondary databases are identical.

    • Se i percorsi sono identici:If the paths are identical:

      Nel computer che ospita la replica secondaria, ripristinare il backup completo nel modo seguente: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 i percorsi sono diversi:If the paths differ:

      Se il percorso del database secondario è diverso dal percorso del database primario, ad esempio perché le lettere di unità non corrispondono, per creare il database secondario è necessario che l'operazione di ripristino includa una clausola 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 il nome di percorso del database primario è diverso dal nome di percorso dei database secondari, non è possibile aggiungere un file.If the path names of the primary and secondary databases differ, you cannot add a file. Alla ricezione del log relativo all'operazione di aggiunta del file, l'istanza del server della replica secondaria tenta infatti di salvare il nuovo file nello stesso percorso utilizzato dal database primario.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.

      Ad esempio, tramite il comando seguente viene ripristinato un backup di un database primario che risiede nella directory di dati dell'istanza predefinita di SQL Server 2017SQL Server 2017, C:\Programmi\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. Con l'operazione di ripristino il database verrà spostato nella directory dei dati di un'istanza remota di SQL Server 2017SQL Server 2017 denominata (Always On1) che ospita la replica secondaria su un altro nodo del 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. In questo percorso, i file di dati e di log vengono ripristinati nella directory C:\Programmi\Microsoft SQL Server\MSSQL13.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 . Per l'operazione di ripristino viene utilizzata l'opzione WITH NORECOVERY per lasciare il database secondario nel database di ripristino.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. Dopo il ripristino del backup completo, è necessario creare un backup del log nel database primario.After you restore the full backup, you must create a log backup on the primary database. Ad esempio, l'istruzione Transact-SQLTransact-SQL seguente esegue il backup del log in un file di backup denominato E:\MyDB1_log.bak:For example, the following Transact-SQLTransact-SQL statement backs up the log to the a backup file named E:\MyDB1_log.bak:

    BACKUP LOG MyDB1   
      TO DISK = 'E:\MyDB1_log.bak'   
    GO  
    
  6. Prima di creare il join del database alla replica secondaria, è necessario applicare il backup del log richiesto ed eventuali backup del log successivi.Before you can join the database to the secondary replica, you must apply the required log backup (and any subsequent log backups).

    Ad esempio, l'istruzione Transact-SQLTransact-SQL seguente ripristina il primo log da C:\MyDB1.bak:For example, the following Transact-SQLTransact-SQL statement restores the first log from C:\MyDB1.bak:

    RESTORE LOG MyDB1   
      FROM DISK = 'E:\MyDB1_log.bak'   
        WITH FILE=1, NORECOVERY  
    GO  
    
  7. Se vengono eseguiti altri backup del log prima del join del database alla replica secondaria, è inoltre necessario ripristinare tutti questi backup, in sequenza, nell'istanza del server che ospita la replica secondaria utilizzando 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.

    Ad esempio, l'istruzione Transact-SQLTransact-SQL seguente ripristina altri due log da E:\MyDB1_log.bak:For example, the following Transact-SQLTransact-SQL statement restores two additional logs from E:\MyDB1_log.bak:

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

Utilizzo di PowerShell Using PowerShell

Per preparare un database secondarioTo prepare a secondary database

  1. Se è necessario creare un backup recente del database primario, spostarsi nella directory (cd) dell'istanza del server che ospita la replica primaria.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. Usare il cmdlet Backup-SqlDatabase per creare i singoli backup.Use the Backup-SqlDatabase cmdlet to create each of the backups.

  3. Spostarsi nella directory (cd) dell'istanza del server che ospita la replica secondaria.Change directory (cd) to the server instance that hosts the secondary replica.

  4. Per ripristinare il database e i backup del log di ogni database primario, usare il cmdlet restore-SqlDatabase , specificando il parametro di ripristino NoRecovery .To restore the database and log backups of each primary database, use the restore-SqlDatabase cmdlet, specifying the NoRecovery restore parameter. Se i percorsi di file differiscono tra i computer in cui sono ospitate la replica primaria e la replica secondaria di destinazione, usare anche il parametro di ripristino 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.

    Nota

    Per visualizzare la sintassi di un cmdlet, usare il cmdlet Get-Help nell'ambiente SQL ServerSQL Server PowerShell.To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL ServerSQL Server PowerShell environment. Per altre informazioni, vedere Get Help SQL Server PowerShell.For more information, see Get Help SQL Server PowerShell.

  5. Per completare la configurazione del database secondario, è necessario creare un join dello stesso al gruppo di disponibilità.To complete configuration of the secondary database, you need to join it to the availability group. Per altre informazioni, vedere Creare un join di un database secondario a un gruppo di disponibilità (SQL Server).For more information, Join a Secondary Database to an Availability Group (SQL Server).

    Per impostare e utilizzare il provider PowerShell per SQL ServerTo set up and use the SQL Server PowerShell provider

Script e comando di backup e ripristino di esempio Sample backup and restore script and command

Tramite i comandi di PowerShell riportati di seguito viene eseguito il backup completo di un database e del log delle transazioni in una condivisione di rete e vengono ripristinati i backup dalla condivisione.The following PowerShell commands back up a full database backup and transaction log to a network share and restore those backups from that share. In questo esempio si presuppone che il percorso del file in cui viene ripristinato il database corrisponda al percorso del file nel quale è stato eseguito il backup del database.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"  

Passaggi successivi Next steps

Per completare la configurazione del database secondario, creare un join del database appena ripristinato al gruppo di disponibilità.To complete configuration of the secondary database, join the newly restored database to the availability group. Per altre informazioni, vedere Creare un join di un database secondario a un gruppo di disponibilità (SQL Server).For more information, see Join a Secondary Database to an Availability Group (SQL Server).

Vedere ancheSee also

Panoramica di Gruppi di disponibilità AlwaysOn (SQL Server) Overview of Always On Availability Groups (SQL Server)
BACKUP (Transact-SQL) BACKUP (Transact-SQL)
Argomenti dell'istruzione RESTORE (Transact-SQL) RESTORE Arguments (Transact-SQL)
RESTORE (Transact-SQL) RESTORE (Transact-SQL)
Risolvere i problemi relativi a una operazione di aggiunta file non riuscita (Gruppi di disponibilità AlwaysOn)Troubleshoot a Failed Add-File Operation (Always On Availability Groups)