2

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.
  • Just spreading the word but great PowerShell module is available for backup/restore operations: dbatools. It handles all the SMO coding for you. – Shawn Melton Aug 11 '17 at 13:38
  • Shawn thanks for a word. I know dbatools and I'm using them, but I want to learn something from PS at this point. – ddragman Aug 16 '17 at 6:06
5

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
  • Scott thanks for a link and answer. Adding this two lines in my script solved the error when running script from PS console. I'm still getting failed attempt runing this script from SQL Agent job step as CmdExec or Powershell type. I will dig deeper to resolve this. – ddragman Aug 10 '17 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.