Scripting out AG code with Powershell

Question

Post reply

Scripting out AG code with Powershell

Brandie Tarvin

SSC Guru

Points: 172983
More actions
October 4, 2023 at 2:49 pm

#4301329
I'm trying to script out an availability group in SQL and found this neat article here with a powershell script: Use PowerShell to script existing Availability Group creation scripts! – SQLServerCentral
I KNEW I LOVED THIS SITE.
Well, I'm running into an error when I try to run the script. The powershell gets down to actually creating the T-SQL File, writes all the comments, then bombs out with this:
Could not load file or assembly 'Microsoft.SqlServer.Dmf.Common, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its
dependencies. The system cannot find the file specified.
At C:\TEMP\Scripts\ScriptMyAvailabilityGroups.ps1:25 char:5
+ $scriptr.Script($ag) | Out-File -FilePath $OutFile -Encoding ASCI ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], FileNotFoundException
+ FullyQualifiedErrorId : System.IO.FileNotFoundException
The thing is, this specific assembly/file isn't being specifically called out in the powershell script. So I can't remove a version number or alter the name. Here's the script in its entirely. Does anyone have any thoughts on how I can get it working?
```
# SQL Server you want to run this against
$SQLServer = 'Server\Instance'
# Setup pathing and environment based on the script location
$Invocation = (Get-Variable MyInvocation -Scope 0).Value
$ScriptLocation = Split-Path $Invocation.MyCommand.Path
$ScriptName = $Invocation.MyCommand.Name.Replace(".ps1","")
$ScriptFullPath = $Invocation.MyCommand.Path
# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SQLObj = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer
$SQLObj.ConnectionContext.Connect()
foreach ($ag in ($SQLObj.AvailabilityGroups )){
$SQLINST = $SQLServer.Replace('\','_')
$AGName = $ag.Name
$Dttm = (Get-Date -Format 'yyyyMMdd_hhmm')
$OutFile = "$ScriptLocation\AGInfo\$SQLINST\${AGname}_${Dttm}.sql"
if (!(Test-Path -Path $OutFile -PathType Leaf)) {
New-Item -Path $OutFile -ItemType File -Force
}
Write-output "Scripting Availability Group [$AGName] to '$OutFile'"
'/*' | Out-File -FilePath $OutFile -Encoding ASCII -Force
$ag | Select-Object -Property * | Out-File -FilePath $OutFile -Encoding ASCII -Append
'*/' | Out-File -FilePath $OutFile -Encoding ASCII -Append
$scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SQLObj)
$scriptr.Script($ag) | Out-File -FilePath $OutFile -Encoding ASCII -Append
}
```
Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
October 9, 2023 at 12:19 pm

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135189 More actions · Answer 1

did you actually install SMO for SQL2016 ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Ant-Green SSC Guru Points: 113340 More actions · Answer 2

SMO should be version agnostic.

Can you use DBATools at all instead of writing your own PowerShell?

A combination of Get-DbAvailabilityGroup and Export-DbaScript would be my way to do it rather than trying to code anything.