There is a database in AG and you need to drop it and restore from backup (which is sometimes a transition from developer to prod )
You don’t need any prerequisite for cluster nodes and AG names.
The script find the instance in local windows server and find the db AG nodes and listener.
So finally you need the database name and the location of the backup folder which can be an Ola backup solution folder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
$db="AdventureWorks" $BackupDirectory= "\\backupserver01\mssql_bkp\SQLSERVER15\AdventureWorks" $SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" } #find AG: $AGGroup =Get-DbaAgDatabase -SqlInstance $SqlServersOnline[0] | Where-Object {$_.Name -eq $db} $SQLListener = Get-DbaAgListener -SqlInstance $SqlServersOnline[0] | Where-Object {$_.AvailabilityGroup -eq $AGGroup.AvailabilityGroup} # connect to listener to get primary and secondary $availabilityGroupReplicas = Get-DbaAgReplica -SqlInstance $SQLlistener.Name -AvailabilityGroup $AGGroup.AvailabilityGroup $PrimaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Primary').Name $SecondaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Secondary').Name $AvailabilityGroup = $AGGroup.AvailabilityGroup # remove db from AG-s Remove-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db Remove-DbaDatabase -SqlInstance $secondaryReplica -Database $db -Confirm:$false # check restore script with -OutputScriptOnly # Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution -OutputScriptOnly # usefull paratmeters if needed: -RestoreTime '2022-01-26 07:15:00' -WithReplace -OutputScriptOnly -MaintenanceSolution Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution # -OutputScriptOnly # check and set recovery model if needed if ( (Get-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db).recoverymodel -eq "Simple") ` { Set-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db -RecoveryModel Full ; Backup-DbaDatabase -SqlInstance $PrimaryReplica -Database $db -FilePath NUL } # add database to primary AG Add-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db -SeedingMode Automatic |