HiveBrain v1.2.0
Get Started
← Back to all entries
patternshellMinor

Missing assembly when running PowerShell script using SMO

Submitted by: @import:stackexchange-dba··
0
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

.\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log


Executing 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:\Log


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

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

Code Snippets

#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

Context

StackExchange Database Administrators Q#183155, answer score: 8

Revisions (0)

No revisions yet.