Restore Output into variable

HenrikSHenrikS Posts: 8 New member
in SQL Backup
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:
· Share on Twitter

Best Answer

  • petey2petey2 Posts: 87 Silver 3
    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
    · Share on Twitter

Answers

Sign In or Register to comment.