Scripting out AG code with Powershell

  • 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


    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=, 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
    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:[/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.

  • did you actually install SMO for SQL2016 ?


    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

  • 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.

  • 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