Invoke-sqlcmd wait for Restoration to complete before going to next task

Question

I'm running this powershell script in order to restore a database on sql server.

$Query = @" EXEC msdb.dbo.sp_start_job N'Job'; GO "@

Invoke-Sqlcmd -ServerInstance .... -Query $Query -Verbose

I get this output => VERBOSE: 'job' started successfully.

When I check SQL server I find that the Database in restoring state but didn't finish. My problem is that I need to make sure that the database was successfully restored before passing to the next tasks.

I made a deep research on how to do it using other methods, like using the dbo.restorehistory to get the result but it's not really efficient.

Is there a way to make Invoke-sqlCmd command wait for the job to finish before continuing the execution of the script?

Perhaps put the rest of the script in another job step, which will get executed only when this is finished. You can make a Powershell job step — Charlieface, Oct 01 '21 at 11:14
Thank you for your proposition, it didn't solve the issue, I need to have Invoke-sqlcmd command passed only after the full completion of the restauration. — arhe10, Oct 01 '21 at 13:51
Have you considered using Invoke-SqlCmd to execute the restore database code directly, and thus synchronously, rather than firing off an asynchronous SQL Agent job? — AlwaysLearning, Oct 01 '21 at 14:50
Probably check the DM view for the restore command from `sys.dm_exec_requests` like https://www.mssqltips.com/sqlservertip/2343/how-to-monitor-backup-and-restore-progress-in-sql-server/. Or you could try querying the job status?: https://stackoverflow.com/a/18062236/7411885 — Cpt.Whale, Oct 01 '21 at 18:49
Thank you for your propositions, I proposed to do the restauration directly but our team wants to keep the steps in sql server and only call the SQL agent jobs from the pipelines. I found a way to make sure that the restauration was done successfully I'll post it. — arhe10, Oct 04 '21 at 15:00

score 0 · Accepted Answer · answered Oct 06 '21 at 09:49

I found a solution to make sure that the restauration completed successfuly by using the stored procedure sp_help_jobhistory , here's the link for documentation: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-help-jobhistory-transact-sql?view=sql-server-ver15.

Here's the script:

[string]$output;
$Query =@"
USE msdb;
GO

declare @temp table
(
    job_id varchar(255),
    job_name varchar(255),
    run_status varchar(255),
    run_date varchar(255),
    run_time varchar(255),
    run_duration varchar(255),
    operator_emailed varchar(255),
    operator_netsent varchar(255),
    operator_paged varchar(255),
    retries_attempted varchar(255),
    server varchar(255)
);
INSERT @temp  Exec dbo.sp_help_jobhistory @job_name = N'JobName';
select top 1 run_status from @temp order by run_date Desc, run_time ;

GO

"@
$output =  Invoke-Sqlcmd -ServerInstance xxxxxx -Database xxxxx  -Username xxx -Password  xxxx  -Query $Query  
$result = $output.itemArray
if ($result -eq '1'){
    Write-Host "Restauration succeded"
}
else { Write-Host "Restauration failed"}

I used a temporary table to read the content of the stored procedure, then I got the last job executed by ordering the result of the select query.

Also I added this script in a step before to test the restauration completion, because I can't access the database while it is in the restoration phase.

[string]$sucess = 'false'
$i = 0;
$ErrorActionPreference= 'silentlycontinue'
$query =@"
    SELECT * FROM tiers Where id = 1;
    GO
"@
do{
    Invoke-Sqlcmd -ServerInstance xxxxxx -Database xxxxx  -Username xxx -Password  xxxx  -Query $query   
    if($?){
        $sucess = 'true';
        break;
    }
    Write-Host "Echec"
    start-sleep -Seconds 60
    $i++
} While ($i -le 2 )
if ($sucess -eq 'true'){
Write-Host "Success"
}
Else { 
    Write-Host "Wait for too long, you need to manually check the restauration steps"
}

You can simply add the do...While in the first script, in my case I need to test the completion of restauration seperately from the script that checks the status of it.

Invoke-sqlcmd wait for Restoration to complete before going to next task

1 Answers1