1

I am trying to extract a dacpac from a SQL Server database via Powershell script. Using this MS example found here Extract a DAC using PowerShell

The code is as follows:

Import-Module SqlServer

## Set a SMO Server object to the default instance on the local computer.  
CD SQLSERVER:\SQL\localhost\DEFAULT  
$srv = get-item .  
  
## Specify the database to extract to a DAC.  
$dbname = "MyDB"  
  
## Specify the DAC metadata.  
$applicationname = "MyApplication"  
$version = "1.0.0.0"  
$description = "This DAC defines the database used by my application."  
  
## Specify the location and name for the extracted DAC package.  
$dacpacPath = "C:\MyDACs\MyApplication.dacpac"  
  
## Extract the DAC.  
$extractionunit = New-Object Microsoft.SqlServer.Management.Dac.DacExtractionUnit($srv, $dbname, $applicationname, $version)  
$extractionunit.Description = $description  
$extractionunit.Extract($dacpacPath)

I added in the call to load the SqlServer module, at the beginning of the script, because this issue had been brought to my attention (right or wrong I don't know)

The error message I am getting is:

New-Object: C:\xxxxxxx\ .\extractDac.ps1:21:19
Line |
  21 |  … ctionunit = New-Object Microsoft.SqlServer.dacpac($srv, $dbname, $app …
     |                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Cannot find type [Microsoft.SqlServer.dacpac]: verify that the assembly containing this type is loaded.
InvalidOperation:

I have installed Microsoft.SqlServer.SqlManagementObjects.170.13.0 in C:\Program Files\PackageManagement\NuGet\Packages And checked to see the package is there.

I used the following to check whether the assembly, containing this type, is loaded:

[System.AppDomain]::CurrentDomain.GetAssemblies() | Where-Object Location | Sort-Object -Property FullName | Select-Object -Property FullName, Location, GlobalAssemblyCache, IsFullyTrusted | Out-GridView

These are what I have:

Microsoft.SqlServer.Management.Dmf
Microsoft.SqlServer.Management.PSProvider
Microsoft.SqlServer.Management.PSSnapins
Microsoft.SqlServer.Management.RegisteredServers
Microsoft.SqlServer.Management.Sdk.Sfc

So the package has been installed but the assemblies are not loaded? This doc Load the SMO Assemblies in Windows PowerShell suggests to use the code example for loading the SQL Server Management Objects "If your script references a SMO object before the first command that references the provider or cmdlets from the SQL Server snap-ins."

What can I try next?

halfer
  • 19,824
  • 17
  • 99
  • 186
Dave
  • 687
  • 7
  • 15
  • 1
    Try doing `Add-Type -AssemblyName Microsoft.SqlServer.Management.Dac` before your code – Santiago Squarzon Feb 21 '23 at 03:01
  • Thanks. But it generates error `Cannot find path` I thought I would need to add `-path` Your suggestion got me searching. This looked helpful https://stackoverflow.com/questions/49278390/powershell-add-type-path-locking-file – Dave Feb 21 '23 at 03:15
  • 1
    You can use the path to the `.dll`, no idea where it is placed though – Santiago Squarzon Feb 21 '23 at 03:31
  • Have you tried to do it this way: https://learn.microsoft.com/en-us/sql/powershell/sql-server-powershell?view=sql-server-ver16 ? – Alex Feb 21 '23 at 08:53

0 Answers0