Add-SqlAvailabilityDatabase

Adds one or more primary databases or joins one or more secondary databases to the availability group specified inthe InputObject or Path parameter.
Add-SqlAvailabilityDatabase [[-Path] <string>] -Database* <string[]> [-Script] [-Confirm] [-WhatIf][<CommonParameters>]
Add-SqlAvailabilityDatabase [-InputObject*] <AvailabilityGroup> -Database* <string[]> [-Script] [-Confirm] [-WhatIf][<CommonParameters>]

The Add-SqlAvailabilityDatabase cmdlet adds one or more primary databases or joins one or more secondary databases or to the availability group specified in either the InputObject or Path parameter. To add a databases to an availability group, execute this cmdlet at the server instance that hosts the primary replica and specify one or more local user databases. To join a secondary database to the availability group, manually prepare the secondary database on the server instance that hosts the secondary replica, and then execute this cmdlet at the server instance that hosts the secondary replica.

Parameters
-Database <string[]>

  • This value is required
  • Accepts pipeline input ByValue

Specifies an array of one or more user databases to add or join to the availability group specified in the Path or InputObject parameter. These databases must reside on the local instance of SQL Server. Note that a given database can belong to only one availability group.

-InputObject <AvailabilityGroup>

  • This value is required
  • Accepts pipeline input ByValue

The AvailabilityGroup object of the availability group to which you are adding or joining databases.

-Path <string>

The path to the availability group. This is an optional parameter. If not specified, the value of this parameter defaults to the current working location.

-Script [<SwitchParameter>]

Outputs a Transact-SQL script that performs the task executed by this cmdlet.

-Confirm [<SwitchParameter>]

Prompts you for confirmation before executing the command.

-WhatIf [<SwitchParameter>]

Describes what would happen if you executed the command without actually executing the command.

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Inputs

Microsoft.SqlServer.Management.Smo.AvailabilityGroup

Examples
  1. This command adds the database ‘MyDatabase’ to the availability group ‘MyAG’:
    C:PS> Add-SqlAvailabilityDatabase -Path SQLSERVER:SQLPrimaryServerInstanceNameAvailabilityGroupsMyAG -Database "MyDatabase"
    

    This command, which must be executed on the primary server instance of the availability group, does not prepare the secondary databases for data synchronization.

  2. This command joins a secondary database, ‘MyDatabase’, to the availability group ‘MyAG’ on one of the server instances that hosts a secondary replica:
    C:PS> Add-SqlAvailabilityDatabase -Path SQLSERVER:SQLSecondaryServerInstanceNameAvailabilityGroupsMyAG -Database "MyDatabase"
    
  3. This example shows the full process for preparing a secondary database from a database on the server instance that hosts the primary replica of an availability group, adding the database to an availability group (as a primary database), and then joining the secondary database to the availability group:
    C:PS> $DatabaseBackupFile = "\sharebackupsMyDatabase.bak"
    $LogBackupFile = "\sharebackupsMyDatabase.trn"
    $MyAgPrimaryPath = "SQLSERVER:SQLPrimaryServerInstanceNameAvailabilityGroupsMyAg"
    $MyAgSecondaryPath = "SQLSERVER:SQLSecondaryServerInstanceNameAvailabilityGroupsMyAg"
    
       Backup-SqlDatabase -Database "MyDatabase" -BackupFile $DatabaseBackupFile -ServerInstance 
       "PrimaryServerInstanceName"
       Backup-SqlDatabase -Database "MyDatabase" -BackupFile $LogBackupFile -ServerInstance "PrimaryServerInstanceName" 
       -BackupAction 'Log'
       Restore-SqlDatabase -Database "MyDatabase" -BackupFile $DatabaseBackupFile -ServerInstance 
       "SecondaryServerInstanceName" -NoRecovery
       Restore-SqlDatabase -Database "MyDatabase" -BackupFile $LogBackupFile -ServerInstance 
       "SecondaryServerInstanceName" -RestoreAction 'Log' -NoRecovery
       Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database "MyDatabase"
       Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database "MyDatabase"

    First, the example backs up the database and its transaction log. Then the example restores the database and log backups to the server instances that host a secondary replica. The example calls Add-SqlAvailabilityDatabase twice: first on the primary replica to add the database to the availability group, and then on the secondary replica to join the secondary database on that replica to the availability group. If you have more than one secondary replica, restore and join the secondary database on each of them.

  4. This command outputs the Transact-SQL script that adds the database ‘MyDatabase’ to the availability group ‘MyAG’:
    C:PS> Add-SqlAvailabilityDatabase -Path SQLSERVER:SQLPrimaryServerInstanceNameAvailabilityGroupsMyAG -Database "MyDatabase" -Script
    
Additional Notes