We help IT Professionals succeed at work.
Troubleshooting Question

Powershell issue during SQL database "bak" file restore.

67 Views
Last Modified: 2020-09-25
Hi

I am trying to run powershell script to restore SQL database.

However, when I executed that script I received an error below.

Can you please help me to fix that syntax or script problem?

PSSQLSERVER:\>C:\Users\xxxx\Downloads\SQL_POWER_BACKUP\templete_restrore.ps1
0
1
Exception calling "SqlRestore" with "1" argument(s): "Restore failed for Server 'default'. "
At C:\Users\rejmanm\Downloads\SQL_POWER_BACKUP\templete_restrore.ps1:35 char:1
+ $dbRestore.SqlRestore($sqlServer)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : FailedOperationException
 
...SQL Database Crucible_Dev Restored Successfullyy...



Powershell Script to restore SQL database:

# Set SQL Server instance name
$sqlName= "Servername Instance"

# Set new or existing databse name to restote backup
$dbname= "Cruci.dev"

# Set the existing backup file path
$backupPath= "I:\Backup_common_databases\Cruci_dev.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

# Connect SQL Server.
$sqlServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlName

# Create SMo Restore object instance
$dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")

# Set database and backup file path
$dbRestore.Database = $dbname
$dbRestore.Devices.AddDevice($backupPath, "File")

# Set the databse file location
$dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreFile.PhysicalFileName = $sqlServer.Information.MasterDBPath + "\" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName = $dbname + "_Log"
$dbRestoreLog.PhysicalFileName = $sqlServer.Information.MasterDBLogPath + "\" + $dbRestore.Database + "_Log.ldf"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

# Call the SqlRestore mathod to complete restore database
$dbRestore.SqlRestore($sqlServer)

Write-Host "...SQL Database"$dbname" Restored Successfully..."


Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
First of all: Please edit your post and use [code][/code] tags (the </> button in the toolbar). This makes reading and working with code simpler.

For your error: PowerShell has error handling. Thus refactor your code (clean code is your friend) into an initialization and execution branch. Either as normal methods or as cmdlet. Then you can add error handling for both steps and you can trace down where the error happens.

Further more, you should add a connection test after creating the server object as well as an output to display to what server you're connected. Cause the error message indicates that the connection failed (no servers is named default).

Look into the SQL Server cmdlets and into Restore-SqlDatabase.

p.s. when using strings in PS, you should use single quotes for all strings as long as you don't need string expansion.
DBAduck - Ben MillerSQL Server Architect
CERTIFIED EXPERT

Commented:
The first thing is that there is more to the error than you see because SMO is layered a couple deep. Run this code after it fails to see the entire message and post that.

$error[0].exception
$error[0].exception.innerexception
$error[0].exception.innerexception.innerexception

Author

Commented:
I would you be able to make change to the script so it can run.
DBAduck - Ben MillerSQL Server Architect
CERTIFIED EXPERT

Commented:
Run this code and post the error message.  My hunch is it is because of the RelocateFile.

# Set SQL Server instance name
$sqlName = "Servername Instance"

# Set new or existing databse name to restote backup
$dbname = "Cruci.dev"

# Set the existing backup file path
$backupPath = "I:\Backup_common_databases\Cruci_dev.bak"

#Load the required assemlies SMO and SmoExtended.
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

# Connect SQL Server.
$sqlServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $sqlName

# Create SMo Restore object instance
$dbRestore = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Restore

# Set database and backup file path
$dbRestore.Database = $dbname
$dbRestore.Devices.AddDevice($backupPath, "File")

# Set the databse file location
$dbRestoreFile = New-Object -TypeName Microsoft.SqlServer.Management.Smo.RelocateFile
$dbRestoreLog = New-Object -TypeName Microsoft.SqlServer.Management.Smo.RelocateFile

$dbRestoreFile.LogicalFileName = $dbname
$dbRestoreFile.PhysicalFileName = $sqlServer.Information.MasterDBPath + "\" + $dbRestore.Database + "_Data.mdf"
$dbRestoreLog.LogicalFileName = $dbname + "_Log"
$dbRestoreLog.PhysicalFileName = $sqlServer.Information.MasterDBLogPath + "\" + $dbRestore.Database + "_Log.ldf"
$dbRestore.RelocateFiles.Add($dbRestoreFile)
$dbRestore.RelocateFiles.Add($dbRestoreLog)

# Call the SqlRestore mathod to complete restore database
try
{
    $dbRestore.SqlRestore($sqlServer)
    Write-Host "...SQL Database $dbname Restored Successfully..."
}
catch
{
    $ex = $_
    Write-Error $ex.Message
    $ex = $ex.InnerException 
    while ($ex)
    {
        Write-Error $ex.Message
        $ex = $ex.InnerException
    }
}


ste5anSenior Developer
CERTIFIED EXPERT

Commented:
As already said, separate concerns, e,g,

#Requires -Version 5.0

Set-StrictMode -Version 2

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | Out-Null

$INSTANCE_NAME= 'Servername Instance'
$DATABASE_NAME= 'Cruci.dev'
$BACKUP_PATH= 'I:\Backup_common_databases\Cruci_dev.bak'

$relocateFileData = $Null
$relocateFileLog =  $Null
$restore = $Null
$server = $Null

Function Initialize() {
    Write-Host 'Connecting..'
    Try {
        $server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $INSTANCE_NAME
        Write-Host "Connected to $($server.InstanceName).`n"
        # $server # Uncomment for debug proposes.
    }
    Catch {
        Write-Exception $_.Exception
    }

    Write-Host 'Setup backup files..'
    Try {
        $relocateFileData = New-Object('Microsoft.SqlServer.Management.Smo.RelocateFile') | Out-Null
        $relocateFileLog =  New-Object('Microsoft.SqlServer.Management.Smo.RelocateFile') | Out-Null
        $restore = New-Object ('Microsoft.SqlServer.Management.Smo.Restore') | Out-Null
        $restore.Database = $DATABASE_NAME
        $restore.Devices.AddDevice($BACKUP_PATH, 'File')
        $relocateFileData.LogicalFileName = $DATABASE_NAME
        $relocateFileData.PhysicalFileName = "$($server.Information.MasterDBPath)\$($DATABASE_NAME)_Data.mdf"
        $restore.RelocateFiles.Add($relocateFileData)
        $relocateFileLog.LogicalFileName = "$($DATABASE_NAME)_Log"
        $relocateFileLog.PhysicalFileName = "$($server.Information.MasterDBLogPath)\$($DATABASE_NAME)_Log.ldf"
        $restore.RelocateFiles.Add($relocateFileLog)
        Write-Host "Done.`n"
    }
    Catch {
        Write-Exception $_.Exception
    }
}

Function Execute() {
    Write-Host 'Executing..'
    Try {
        $restore.SqlRestore($server)
        Write-Host "Done.`n"
    }
    Catch {
        Write-Exception $_.Exception
    }
}

Function Write-Exception() {
    Param($Exception)
    Write-Host "EXCEPTION: $($Exception.Message)"
    $Exception = $Exception.InnerException
    While ($Exception)
    {
        Write-Host "EXCEPTION: $($Exception.Message)"
        $Exception = $Exception.InnerException
    }
}

Function ScriptMain() {
    Clear-Host
    Initialize
    Execute
}

ScriptMain

Open in new window

Author

Commented:
This message is for the script sent by Ben

I have executed this script but Igot an error.
I ran Powershell script as administrator as well as from Powershell ISE. The same error was generated.

I created an SQL Job to execute Powershell Script. The job w ran successfully but the even log shows this message.

The restore script didn't refresh existing database.

Message
Executed as user: NT Service\SQLSERVERAGENT. 0  1    The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line 0 in a PowerShell script. The corresponding line is ''. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception of type 'Microsoft.PowerShell.Commands.WriteErrorException' was thrown.  '.  Process Exit Code 0.  The step succeeded.

What am I doing wrong?
Do I need to install modules?

Error Log.txt


ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Don't run the script as job, before it works on the normal PS prompt.

For running such a script as job you need complete error handling and to return appropriate exit codes (0 for no error, 0> custom error).

Author

Commented:
Hi ste5an

When I executed your script from Powershell , I received this message.

Connecting..
Connected to .'n
Setup backup files..
EXCEPTION: The property 'Database' cannot be found on this object. Verify that the property exists and can be set.
Executing..
EXCEPTION: You cannot call a method on a null-valued expression.

When I executed your job from SQL Agent Job I receive this message

Message
Unable to start execution of step 1 (reason: line(21): Syntax error).  The step failed.
Line 21 is    Write-Host "Connected to $($server.InstanceName).'n"

I am using PS Version

Get-Host | Select-Object Version

Version      
-------      
5.1.14409.1018
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Too many unnecessary Out-Null..

	Write-Host 'Setup backup files..'
	Try {		
		$relocateFileData = New-Object('Microsoft.SqlServer.Management.Smo.RelocateFile') 
		$relocateFileLog =  New-Object('Microsoft.SqlServer.Management.Smo.RelocateFile') 
		$restore = New-Object ('Microsoft.SqlServer.Management.Smo.Restore') 	
		$restore.Database = $DATABASE_NAME
		$restore.Devices.AddDevice($BACKUP_PATH, 'File')	
		$relocateFileData.LogicalFileName = $DATABASE_NAME		
		$relocateFileData.PhysicalFileName = "$($server.Information.MasterDBPath)\$($DATABASE_NAME)_Data.mdf"
		$dummy = $restore.RelocateFiles.Add($relocateFileData)	
		$relocateFileLog.LogicalFileName = "$($DATABASE_NAME)_Log"
		$relocateFileLog.PhysicalFileName = "$($server.Information.MasterDBLogPath)\$($DATABASE_NAME)_Log.ldf"
		$dummy = $restore.RelocateFiles.Add($relocateFileLog)	
		Write-Host "Done.`n"
	}

Open in new window

Author

Commented:
PS after modification.txt

Error:

Connecting..
Connected to .

Setup backup files..
Done.

Executing..
EXCEPTION: You cannot call a method on a null-valued expression.

PS C:\Users\rejmanm> $Error
You cannot call a method on a null-valued expression.
At C:\Users\user\Downloads\SQL_POWER_BACKUP\REstore-TEST.ps1:51 char:9
+         $restore.SqlRestore($server)
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
Senior Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you, it worked this time. 
DBAduck - Ben MillerSQL Server Architect
CERTIFIED EXPERT

Commented:
Not a great way to write PowerShell for this. Using $script scoped variables works, but is not a best practice.

Glad you got it to work. This is a simpler way to look at it.  All the stuff should be in a try .. catch so that when one thing fails, it goes into the catch. If you don't get a server, it makes no sense to go into initialize the backup stuff, etc.

#Requires -Version 5.0

Set-StrictMode -Version 2

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Smo') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SmoExtended') | Out-Null


Function Write-Exception
{
    Param ($Exception)
    
    Write-Host "EXCEPTION: $($Exception.Message)"
    $Exception = $Exception.InnerException
    While ($Exception)
    {
        Write-Host "EXCEPTION: $($Exception.Message)"
        $Exception = $Exception.InnerException
    }
}

function Restore-MyBackup
{
    param (
        [string]$InstanceName,
        [string]$DatabaseName,
        [string]$BackupPath
    )
    
    try
    {
        $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -Args $InstanceName
        Write-Host "Connected to $($server.DomainInstanceName)."
        
        $relocateFileData = New-Object('Microsoft.SqlServer.Management.Smo.RelocateFile')
        $relocateFileLog = New-Object('Microsoft.SqlServer.Management.Smo.RelocateFile')
        $restore = New-Object ('Microsoft.SqlServer.Management.Smo.Restore')
        $restore.Database = $DatabaseName
        $restore.Devices.AddDevice($BackupPath, 'File')
        $relocateFileData.LogicalFileName = $DatabaseName
        $relocateFileData.PhysicalFileName = "$($server.Information.MasterDBPath)\$($DatabaseName)_Data.mdf"
        $null = $restore.RelocateFiles.Add($relocateFileData)
        $relocateFileLog.LogicalFileName = "$($DatabaseName)_Log"
        $relocateFileLog.PhysicalFileName = "$($server.Information.MasterDBLogPath)\$($DatabaseName)_Log.ldf"
        $null = $restore.RelocateFiles.Add($relocateFileLog)
        Write-Host "Done setting up variables.`n"
        
        $restore.SqlRestore($server)
        Write-Host "Done Restoring $DatabaseName.`n"
        
    }
    catch
    {
        Write-Exception $_
        throw
    }
    
}

$INSTANCE_NAME = '<placeholder>' #'Servername Instance'
$DATABASE_NAME = 'Cruci.dev'
$BACKUP_PATH = 'I:\Backup_common_databases\Cruci_dev.bak'

Clear-Host
Restore-MyBackup -InstanceName $INSTANCE_NAME -DatabaseName $DATABASE_NAME -BackupPath $BACKUP_PATH


ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Not a great way to write PowerShell for this.
This was not the goal of this exercise. Nor was the goal to write a script for production use. The goal was to isolate the error. And using SoC is also in PowerShell a common approach.
DBAduck - Ben MillerSQL Server Architect
CERTIFIED EXPERT

Commented:
I get it. I was just taking the approach that the content will be out there for a long time to come and should help educate.
I am not trying to be controversial, so I am sorry if my comment sounded like that. Solution was acknowledged and my post after was only meant to help content live on.

Author

Commented:
What will be the best and very efficient to backup, copy and restore bak file for sql (database).

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Without knowing your exact context, I would use the SQL Server PowerShell cmdlets instead of coding it myself as already posted at the end of this post,
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Sending an e-mail with PowerShell is not that simple, as there is no built-in cmdlet.

You should look into

https://social.technet.microsoft.com/Search/en-US?query=powershell%20email&beta=0&ac=2

for existing scripts.

If you have database mail setup on your SQL Server, then I would consider doing the restore and sending of a message as stored procedure.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.