The customer for whom I am working, need that I check all SQL Server Instances (about 40) for a database that is not joined in the availability group (AG). I am a little bit lazy to do that manually, so I decide to write a PowerShell. Of course, I am using dbatools (thanks to all contributors for that tool:-).
Ok first we need to ensure, that the dbatools modules are installed. How to do that? Just visit https://dbatools.io/getting-started/
So I start with the Import-Module command line.
Import-Module -Name dbatools -DisableNameChecking
The next thing is to specified the AG Name. That is the only one thing I must type manually. Of course, I can also specify the backup path and another option but for me, this one is enough.
$AvailabilityGroupName = 'AG-Name'
$primaryInstance = $null
$secondaryInstances = @{ }
After that, I need to find the primary and secondary instances.
$replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName
$primaryInstance = $replicas | Where-Object Role -eq Primary | Select-Object -ExpandProperty name
$secondaryInstances = $replicas | Where-Object Role -ne Primary | Select-Object -ExpandProperty name
Then I search for user databases, which are not joined to AG and have only Full recovery mode. Then I want to have only the “name” property of those databases
$dbsname = Invoke-Sqlcmd -ServerInstance $primaryInstance -Database master -Query "SELECT name From sys.databases
WHERE database_id > 4
AND group_database_id IS NULL
AND recovery_model_desc = 'FULL'"
$dbsname = $dbsname.name
And finally, for each database stored in $dbsname variable, I run Add-DbaAgDatabase command. Add-DbaAgDatabase command makes a backup for all these databases (use the default backup path from the instance) and restore it on secondary nodes.
foreach ($dbname in $dbsname) {
Add-DbaAgDatabase -SqlInstance $primaryInstance -AvailabilityGroup $AvailabilityGroupName -Database $dbname -Verbose
}
Thats it. Before go in production make sure you test it first in dev environment.
Any likes and dislikes are welcome. I am open to suggestions and improvements.
And here is the whole script
Import-Module -Name dbatools -DisableNameChecking
$AvailabilityGroupName = 'AG-Name'
$primaryInstance = $null
$secondaryInstances = @{ }
$replicas = Get-DbaAgReplica -SqlInstance $AvailabilityGroupName
$primaryInstance = $replicas | Where-Object Role -eq Primary | Select-Object -ExpandProperty name
$secondaryInstances = $replicas | Where-Object Role -ne Primary | Select-Object -ExpandProperty name
$dbsname = Invoke-Sqlcmd -ServerInstance $primaryInstance -Database master -Query "SELECT name From sys.databases WHERE database_id > 4 AND group_database_id IS NULL and recovery_model_desc = 'FULL'"
$dbsname = $dbsname.name
foreach ($dbname in $dbsname) {
Add-DbaAgDatabase -SqlInstance $primaryInstance -AvailabilityGroup $AvailabilityGroupName -Database $dbname -Verbose
}
Home 