Sql-server – Missing assembly when running PowerShell script using SMO

powershellsmosql-server-2016sql-server-agentwindows-server

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.

Best Answer

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

Related Solutions

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

The problem is that the PowerShell session is using the SqlServer provider by default. I added this statement into the script before the Out-File cmdlet:

Set-Location c:

This changed the provider to the FileSystem provider, and allowed the Out-File cmdlet to correctly find the file share and create the file.

This article contrasts how SQLPS loads in SQL Server 2008 R2 vs. SQL Server 2012/2014. The SQL Provider is loaded using the 'Add-PSSnapin' cmdlet in SQL Server 2008 R2, while starting with SQL Server 2012, the 'Import-Module' cmdlet is used.

The author states: "The outcome of loading the module [with 'Import-Module'] is simply changing to the PSDrive you just loaded." Evidently, the 'Add-PSSnapin' cmdlet left the provider for its SQLPS session unchanged (as FileSystem). That change in cmdlet use, and the resulting session behavior, was what caused my problem.

Sql-server – Execution-Policy Error running Powershell Script in SQL Server Agent

The error you are receiving was actually noted in a connect item but Microsoft shows it as closed won't fix. What is missed in this connect item is the fact that the subsystem for SQLPS is set via a registry key. When and what actually sets this I do not know.

This registry key is found at the path below and on my local box is set to RemoteSigned. Now I do not generally advise changing registry keys but you can try changing this to RemoteSigned and you will likely find your scripts will run without error. It might require restart of SQL Agent service, don't know.

enter image description here HKLM\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps120

Now, with using Unrestricted you can actually cause scripts to receive a prompt when executing the PowerShell script. This may be what is actually generating the error because SQL Agent cannot respond to the prompt or does not know how to handle it. There is really no reason to use that policy setting as RemoteSigned is a sufficient policy to allow scripts you wrote and setup on the server to execute without being prompted.

I would except if you dug into the full error being returned it might contain text similar to the below message. This is the prompt you can receive when setting the execution policy to Unrestricted:

Security Warning

Run only scripts that you trust. While scripts from the Internet can be useful, this script can potentially harm your computer. Do you want to run

[D] Do not run [R] Run once [S] Suspend [?] Help (default is "D"):

Best Answer

Related Solutions

Sql-server – SQL Server 2012 SQL Agent Job with PowerShell step fails on Out-File cmdlet with “ReportWrongProviderType” error

Sql-server – Execution-Policy Error running Powershell Script in SQL Server Agent

Related Question