Just another Select of Ms SQL DBA site
If you get the error:
Get-DbaDatabase] Failure | The certificate chain was issued by an authority that is not trusted.
You can set in dbatools:
|
1 |
Set-DbatoolsConfig -FullName sql.connection.trustcert -Value $true -Register |
or
|
1 2 3 4 5 |
# Set these defaults for all future sessions on this machine Set-DbatoolsInsecureConnection # Set defaults just for this session Set-DbatoolsInsecureConnection -SessionOnly |
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 |
Taking notes – some example:
Download and Install to my computer:
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
Install-Module dbatools
if necessary:
update-module dbatools
OFFLINE:
Copy this directory to offline server:
C:\Program Files\WindowsPowerShell\Modules\dbatools
Set-Location ‘C:\Program Files\WindowsPowerShell\Modules\dbatools’ ##.\install.ps1 ## with internet access
Get-ChildItem -Recurse | Unblock-File