Restore Output into variable

We want to write a script that restore a database and run a procedure after the restore.
Then we want to put the output from both the restore statement and the stored procedure in an email and send it to the department who requested the Restore

The output from the SP is no problem. But how to get the output from the restore ?
I only found:https://forum.red-gate.com/discussion/5237/sqlbackup-return-parameter
But this produce only the Number, not the whole Log Outcome.

Is there any way to do this?




Tagged:

Best Answer

  • petey2petey2 Posts: 58 Silver 2
    Accepted Answer
    One option, using the SINGLERESULTSET option and a temporary table:

    IF OBJECT_ID('tempdb..#sqboutput') IS NOT NULL
        DROP TABLE #sqboutput
    CREATE TABLE #sqboutput (lines nvarchar(4000))
    DECLARE @cmd nvarchar(1024)
    SET @cmd = 'master..sqlbackup ''-sql "RESTORE DATABASE ... WITH ..., SINGLERESULTSET"'''
    INSERT INTO #sqboutput EXEC sp_executesql @cmd
    SELECT * FROM #sqboutput
    DROP TABLE #sqboutput
    SQL Backup - beyond compression

Answers

Sign In or Register to comment.