VSTS PowerShell内联脚本删SQL DB时找不到SMO Server类型

The error pops up because the Azure DevOps (formerly VSTS) PowerShell task environment doesn’t automatically load SQL Server SMO (SQL Server Management Objects) assemblies by default. As a PowerShell beginner, here are two straightforward inline script solutions you can plug directly into your release pipeline—no separate script files required:

Solution 1: Load SMO Assemblies Directly #

This approach targets raw SMO assemblies, and works if your agent machine has the SQL Server SDK installed (usually included with SQL Server or the SQL Server Feature Pack):

# Locate the core SMO assembly (works with most SQL Server versions)
$smoPath = Get-ChildItem "C:\Program Files\Microsoft SQL Server\*\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" | Select-Object -First 1 -ExpandProperty FullName
if (-not $smoPath) {
    throw "SMO assembly not found! Make sure SQL Server SDK is installed on the agent machine."
}

# Load the core assembly and its required dependencies
[System.Reflection.Assembly]::LoadFrom($smoPath) | Out-Null
$dependencies = @("Microsoft.SqlServer.ConnectionInfo.dll", "Microsoft.SqlServer.Management.Sdk.Sfc.dll")
foreach ($dep in $dependencies) {
    $depPath = Get-ChildItem "C:\Program Files\Microsoft SQL Server\*\SDK\Assemblies\$dep" | Select-Object -First 1 -ExpandProperty FullName
    if ($depPath) { [System.Reflection.Assembly]::LoadFrom($depPath) | Out-Null }
}

# Execute your database drop logic (with existence check to avoid errors)
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server(".")
if ($srv.Databases["MyDB"]) {
    $srv.Databases["MyDB"].Drop()
    Write-Host "Successfully dropped database MyDB"
} else {
    Write-Host "Database MyDB doesn't exist - no action taken"
}

Breakdown: #

• It searches standard SQL Server SDK paths to find the SMO assembly (compatible with 2016+ versions)
• Loads critical dependent assemblies that SMO needs to run
• Adds a check to only drop the database if it exists—this prevents unnecessary error messages if MyDB isn’t present

Solution 2: Use the SqlServer PowerShell Module #

This is simpler if your agent has the SqlServer module installed (Microsoft-hosted Windows agents usually have this pre-installed; self-hosted agents can install it via Install-Module SqlServer if needed):

# Import the SqlServer module (it loads all required SMO types automatically)
Import-Module SqlServer -ErrorAction Stop

# Drop the database (with existence check)
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server(".")
if ($srv.Databases["MyDB"]) {
    $srv.Databases["MyDB"].Drop()
    Write-Host "Successfully dropped database MyDB"
} else {
    Write-Host "Database MyDB doesn't exist - no action taken"
}

Why this works: #

The SqlServer module handles loading all required SMO assemblies in the background, so you don’t have to manually locate and load each one yourself.

Quick note on your original script: #

Your core logic was correct, but adding the existence check makes it more robust—without it, you’d get an error if MyDB didn’t exist when the script ran.

内容的提问来源于stack exchange，提问作者Learning Curve