Technical Article

Add new Databases to a AG Setup with PowerShell

,

Edit the variables at the top with the correct names, ports and make sure SPN's are correct and in place I have had some issues using the main AG PowerShell setup script at the end snapping in the replicas due to missing SPN's. This is pretty simple to use and not much code considering what is accomplished as the end result. I hope you are able to make good use of the script. It is kinda fun to sit and watch PowerShell do the work. I added some write-host code so you know where the script is at while it is processing.

$databasename="AGAutomation_Test"
$DatabaseBackupFile = "\\servername\backups\$databasename.bak"  
$LogBackupFile = "\\servername\backups\$databasename.trn"  
$MyAgPrimaryPath = "SQLSERVER:\SQL\servername\Default\AvailabilityGroups\AG_Group_Name"  
$MyAgSecondaryPath = "SQLSERVER:\SQL\servername\Default\AvailabilityGroups\AG_Group_Name"
$SQLPrimaryName="Primaryservername,7119"
$SQLSecondaryName="Secondaryservername,7119"  

Write-Host "Backing up database $databasename to Secondary Node: $SQLSecondaryName" -fore Yellow
Backup-SqlDatabase -Database $databasename -BackupFile $DatabaseBackupFile -ServerInstance $SQLPrimaryName 
Write-Host "Completed Backing up database $databasename to Secondary Node: $SQLSecondaryName" -fore Green 
Write-Host "......."
Write-Host "Backing up log $databasename to Secondary Node: $SQLSecondaryName" -fore Yellow
Backup-SqlDatabase -Database $databasename -BackupFile $LogBackupFile -ServerInstance $SQLPrimaryName -BackupAction 'Log'  
Write-Host "Completed Backing up $databasename log to Secondary Node: $SQLSecondaryName" -fore Green
Write-Host "......." 
Write-Host "Restoring database $databasename to Secondary Node: $SQLSecondaryName" -fore Yellow 
Restore-SqlDatabase -Database $databasename -BackupFile $DatabaseBackupFile -ServerInstance $SQLSecondaryName -NoRecovery 
Write-Host "Completed Restoring database $databasename to Secondary Node: $SQLSecondaryName" -fore Green
Write-Host "......."
Write-Host "Restoring database Log $databasename to Secondary Node: $SQLSecondaryName" -fore Yellow
Restore-SqlDatabase -Database $databasename -BackupFile $LogBackupFile -ServerInstance $SQLSecondaryName -RestoreAction 'Log' -NoRecovery  
Write-Host "Completed Restoring $databasename log to Secondary Node: $SQLSecondaryName" -fore Green
Write-Host "......."
Write-Host "Adding $databasename to Primary Node" -fore Yellow 
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $databasename 
Write-Host "Completed Adding $databasename to Primary Node" -fore Green 
Write-Host "Adding $databasename to Secondary Node" -fore Yellow 
Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $databasename
Write-Host "Completed Adding $databasename to Secondary Node" -fore Green

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content