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
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
} -
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
-
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