Restore-SqlDatabase (poweshell cmd) skip the statment which execute from SQL Server Agent job

Question

Hi I am rewriting a powershell script to refresh the non production env from production env. its working fine while executing from powershell prompt. But when I executed same script from Sql server Agent job. Job is successfully complete . But DB restore has not done it. Very strange . I am not sure why "Restore-SqlDatabase" has not been recognised. any one has any idea

script:
script:

Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query "ALTER DATABASE $TargetDb MODIFY FILE (NAME='$SourceLogicalLogFileName', NEWNAME='$TargetLogicalLogFileName')"

begin script configuration here
$TargetSqlServerInstance = "SQLDEST1" # target server instance
$TargetDb = "CRMDBTST" # target database
$BackupDir = "\sqlbackups\" # directory / share where backups are stored
$SourceLogicalDataFileName = "CRMPRD" # logical data file name of source db
$SourceLogicalLogFileName = "CRMPRD_log" # logical log file name of source db
$TargetLogicalDataFileName = "CRMDBTST" # logical name you want to change logical data file on target db to
$TargetLogicalLogFileName = "CRMDBTST_log" # logical name you want to change logical log file on target db to
$TargetPhysicalDataFileName = "U:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\CRMDBTST.mdf" # full path\file of target db physical data file
$TargetPhysicalLogFileName = "L:\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\CRMDBTST_log.ldf" # full path\file of target db physical log file
$CompatLevel = 130 # compatibility level to set target database to (2019=150, 2017=140, 2016=130, #

import sqlserver module
Import-Module sqlserver
$SQLSERVER="SQLDEST1"
$databaseName="CRMDBTST"

latest full backup file name is dynamically determined and appended to backup directory

$LatestFullBackupFile = Get-ChildItem -Path $BackupDir -Filter *.bak | Sort-Object LastAccessTime -Descending | Select-Object -First 1
$FileToRestore = $BackupDir + '\' + $LatestFullBackupFile

kill any connections in target database
$KillConnectionsSql=
"
USE master
GO
ALTER DATABASE $TargetDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE master
GO
ALTER DATABASE $TargetDb SET MULTI_USER
GO
GO
"
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillConnectionsSql

import sqlserver module
Import-Module sqlserver

restore
$RelocateData = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalDataFileName", "$TargetPhysicalDataFileName")
$RelocateLog = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile("$SourceLogicalLogFileName", "$TargetPhysicalLogFileName")
Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile $FileToRestore -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase

end restore
+++++++++++++++++++++++++++++

Answer 1

Hi @Ashwan ,

It seems you did not add the restore DB poweshell cmd in SQL server agent job. Please check the steps of your agent job. For example;

--------Backup a database based on location;  
Set-Location "SQLSERVER:\SQL\Computer\Instance"   
PS SQLSERVER:\SQL\Computer\Instance> Backup-SqlDatabase -Database "MainDB"  
  
-------Backup the transaction log  
Backup-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupAction Log  
  
------Restore a database from a backup file on a network share  
Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile \\mainserver\databasebackup\MainDB.bak  
  
-------Restore a database transaction log  
Restore-SqlDatabase -ServerInstance "Computer\Instance" -Database "MainDB" -BackupFile "\\mainserver\databasebackup\MainDB.trn" -RestoreAction Log  

Please refer to MS document Backup-SqlDatabase and Restore-SqlDatabase to get detail poweshell scripts.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi Cath, Sorry for the late reply and I was trying few ways to find out the issue. When I execute the job from Agent still with "successfully completed" .I manually tried the script from powershell. Power shell thorugh the error as follows

+++++++++++++++++
Restore-SqlDatabase : System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.
At C:\app\DBA\just-restore-v1_RereshDB.ps1:40 char:1

• Restore-SqlDatabase -ServerInstance "$TargetSqlServerInstance" -Datab ...
• ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
• CategoryInfo : InvalidOperation: (:) [Restore-SqlDatabase], SmoException
• FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand
  +++++++++++++++++++++++++++++++++++++++++++

Action I have done:
The power shell script it self to make it DB in single user mode and i wonder why this is happened

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

$KillConnectionsSql
"
use master
go
declare @execSql varchar(1000), @databaseName varchar(100)
set @databaseName = '$TargetDb'
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
spid <> @@spid
exec (@execSql)"

$SetDBSingleusermode=
"
USE master
GO
ALTER DATABASE $TargetDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
"

Write-Output $KillConnectionsSql

Write-Output $LatestFullBackupFile
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $KillConnectionsSql
Invoke-Sqlcmd -ServerInstance $TargetSqlServerInstance -Query $SetDBSingleusermode

Any ideal please ..

Answer 3

Anyone had similar issue after executing PS script? If I do that manually with the same database in SSMS it works.

Microsoft.Data.SqlClient.SqlError: File 'I:\SQL2017\MSSQL14.SQL2017\MSSQL\DATA\DATABASE1.mdf' is claimed by 'DATABASE2'(3) and 'DATABASE3'(1). T__he WITH MOVE clause can be used to relocate one or more files.__