patternshellMinor
Missing assembly when running PowerShell script using SMO
Viewed 0 times
scriptmissingpowershellsmoassemblyrunningusingwhen
Problem
I wrote a script which takes database name, restored DB name, backup source, data file location and log file location as parameters and performs database restore on SQL Server instance.
Script works fine when ran from PowerShell ISE, but it is throwing missing assemblies error when ran from PowerShell console or term is not recognized as the name of a cmdlewhen error when ran throught SQL Agent job as CmdExec or PowerShell type.
I have tried to add path to assemblies with Add-Type in script, but there is no assemblies named Microsoft.SqlServer.Management.Smo.Server, Microsoft.SqlServer.Management.Smo.Restore... etc.
I am out of ideas as PS newb. Any advice, idea how to solve this problem to be able to run this script within SQL Agent job step?
I am executing the script in ISE and console with command
Executing the script in SQL Agent job
Script
```
Param(
[parameter(Mandatory=$true)]
[String]
$DatabaseName,
[parameter(Mandatory=$true)]
[String]
$NewDatabaseName,
[parameter(Mandatory=$true)]
[String]
$SourceDir,
[parameter(Mandatory=$true)]
[String]
$DataDir,
[parameter(Mandatory=$true)]
[String]
$LogDir)
$file = $DatabaseName+".bak"
$fileFullPath = $SourceDir+"\"+$file
$fullDataFile = $DataDir+"\"+$DatabaseName+".mdf"
$fullLogFile = $LogDir+"\"+$DatabaseName+"_log.ldf"
$ServerName = $env:computername
$server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $ServerName
$restore = New-Object 'Microsoft.SqlServer.Management.Smo.Restore'
$backupDeviceItem = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem
Script works fine when ran from PowerShell ISE, but it is throwing missing assemblies error when ran from PowerShell console or term is not recognized as the name of a cmdlewhen error when ran throught SQL Agent job as CmdExec or PowerShell type.
I have tried to add path to assemblies with Add-Type in script, but there is no assemblies named Microsoft.SqlServer.Management.Smo.Server, Microsoft.SqlServer.Management.Smo.Restore... etc.
I am out of ideas as PS newb. Any advice, idea how to solve this problem to be able to run this script within SQL Agent job step?
I am executing the script in ISE and console with command
.\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\LogExecuting the script in SQL Agent job
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -ExecutionPolicy Bypass -Command C:\Script\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\LogScript
```
Param(
[parameter(Mandatory=$true)]
[String]
$DatabaseName,
[parameter(Mandatory=$true)]
[String]
$NewDatabaseName,
[parameter(Mandatory=$true)]
[String]
$SourceDir,
[parameter(Mandatory=$true)]
[String]
$DataDir,
[parameter(Mandatory=$true)]
[String]
$LogDir)
$file = $DatabaseName+".bak"
$fileFullPath = $SourceDir+"\"+$file
$fullDataFile = $DataDir+"\"+$DatabaseName+".mdf"
$fullLogFile = $LogDir+"\"+$DatabaseName+"_log.ldf"
$ServerName = $env:computername
$server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $ServerName
$restore = New-Object 'Microsoft.SqlServer.Management.Smo.Restore'
$backupDeviceItem = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDeviceItem
Solution
Using some information found in SQL Server PowerShell : How to Restore SQL Server Databases Using SMO and PowerShell, I was able to get past that error. Try adding the following to the very top of your script
#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-NullCode Snippets
#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-NullContext
StackExchange Database Administrators Q#183155, answer score: 8
Revisions (0)
No revisions yet.