Missing assembly when running PowerShell script using SMO

Question

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' ($fileFullPath, 'File')
$restore.Database = $NewDatabaseName
$restore.ReplaceDatabase = $true 
$restore.Devices.Add($backupDeviceItem)
$fileList = $restore.ReadFileList($server)
$dataFileNumber = 0
foreach ($file in $fileList) {
    $relocateFile = New-Object 'Microsoft.SqlServer.Management.Smo.RelocateFile'
    $relocateFile.LogicalFileName = $file.LogicalName
    if ($file.Type -eq 'D') {
        if($dataFileNumber -ge 1) {
            $suffix = "_$dataFileNumber"
        }
        else {

        $suffix = $null;
        }
    $relocateFile.PhysicalFilename = "$DataDir\$DatabaseName$suffix.mdf"
    $dataFileNumber++
    }
    else {

        $relocateFile.PhysicalFileName = "$LogDir\$DatabaseName"+"_log.ldf"    
    }
    $restore.RelocateFiles.Add($relocateFile) | Out-Null
}
$restore.SqlRestore($server)
Exit

Error from PS console

New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Restore]: verify that the assembly containing this ty
pe is loaded.
At C:\Automation\RestoreDB_Script.ps1:32 char:12
+ $restore = New-Object 'Microsoft.SqlServer.Management.Smo.Restore'
+            ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.BackupDeviceItem]: verify that the assembly containin
g this type is loaded.
At C:\Automation\RestoreDB_Script.ps1:33 char:21
+ ... eviceItem = New-Object 'Microsoft.SqlServer.Management.Smo.BackupDevi ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (:) [New-Object], PSArgumentException
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

The property 'Database' cannot be found on this object. Verify that the property exists and can be set.
At C:\Automation\RestoreDB_Script.ps1:34 char:1
+ $restore.Database = $NewDatabaseName
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

The property 'ReplaceDatabase' cannot be found on this object. Verify that the property exists and can be set.
At C:\Automation\RestoreDB_Script.ps1:35 char:1
+ $restore.ReplaceDatabase = $true
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyNotFound

You cannot call a method on a null-valued expression.
At C:\Automation\RestoreDB_Script.ps1:36 char:1
+ $restore.Devices.Add($backupDeviceItem)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Automation\RestoreDB_Script.ps1:37 char:1
+ $fileList = $restore.ReadFileList($server)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\Automation\RestoreDB_Script.ps1:67 char:1
+ $restore.SqlRestore($server)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Message from SQL Agent job step

Executed as user: domain\account. ...id not stop the script:  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'New-Object : The term 'New-Object' is not recognized as the name of a cmdlet, f  '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'unction, script file, or operable program. Check the spelling of the name, or i  f a path was included, verify that the path is correct and try again.  At C:\Scripts\RestoreDB_Script.ps1:31 char:11  + $server = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $Ser ...  +           ~~~~~~~~~~'  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: '    + CategoryInfo          : ObjectNotFound: (New-Object:String) [], CommandN     '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: '   otFoundException    '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: '    + FullyQualifiedErrorId : CommandNotFoundException    '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: '     '  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile  -ExecutionPolicy Bypass -Command C:\Scripts\RestoreDB_Script.ps1 -DatabaseName TestDB -NewDatabaseName TestDB_new -SourceDir V:\Backup -DataDir D:\Data -LogDir L:\Log'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Restore]: ver    '  A job step r...  Process Exit Code 0.  The step succeeded.

Answer 1

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