asked on

Powershell issue during SQL database "bak" file restore.

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..."

ste5an

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 Miller

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

Open in new window

michalek19

ASKER

I would you be able to make change to the script so it can run.

DBAduck - Ben Miller

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

Open in new window

ste5an

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

michalek19

ASKER

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

ste5an

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).

michalek19

ASKER

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

ste5an

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

michalek19

ASKER

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

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

michalek19

ASKER

Thank you, it worked this time.

DBAduck - Ben Miller

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

Open in new window

ste5an

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 Miller

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.

michalek19

ASKER

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

ste5an

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,

ste5an

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.

Powershell issue during SQL database &quot;bak&quot; file restore.

Powershell issue during SQL database "bak" file restore.