Backup T-SQL -- Return variables

Question

Backup T-SQL -- Return variables

GregoryF

SSCertifiable

Points: 6296
More actions
April 21, 2010 at 10:58 am

#221566

I'm trying to do a backup in an SP. Is there any way to get the data outputed by a backup into a variable.
e.g.
Processed 2872 pages for database 'ZZZ', file 'Database' on file 1.
Processed 1 pages for database 'ZZZ', file 'Database_log' on file 1.
BACKUP DATABASE successfully processed 2873 pages in 1.686 seconds (13.959 MB/sec)
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Toby White SSCertifiable Points: 6187 More actions · Answer 1

You can pipe the output somewhere, and then read that output, but you cannot treat with it like you would an output parameter. What do you want to do with these message(s)?

GregoryF SSCertifiable Points: 6296 More actions · Answer 2

Toby White (4/21/2010)
You can pipe the output somewhere, and then read that output, but you cannot treat with it like you would an output parameter. What do you want to do with these message(s)?

As part of a SP that runs a backup, in the audit table I wanted to include the output of the backup command just for the sake of verbosness. Plus it would be nice to see the throughput. Right now, I calculate throughput by taking the size from msdb..backupset of the completed backup and dividing by the total time of the backup.

Also, if the backup fails, the audit table would show the exact reason (though the backup messages are listed in the job history).

I can probably parse job history to pull the information out, I was just hoping there might be an easier way.

/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

Toby White SSCertifiable Points: 6187 More actions · Answer 3

I think you've come up with a smart idea - msdb.dbo.sysjobhistory. I agree with you that there should be an easier way to deal with messages in T-SQL :pinch: I use liitespeed, but store information about backups basically in the form of success or fail. Although litespeed can store metadata like what you are looking for, I don't bother letting it. I looked at one of my job messages for a full backup and it would be a bit hairy to parse. However, I do backups for all the databases in one job.

Executed as user: XXXX, Quest Software Inc. Registered Name: XXXXXX BACKUP ATTACHED FILES successfully processed 1 file(s) Processed 2528504 pages for database 'XXXXX', file 'XXXXXX' on file 1. Processed 7 pages for database 'XXXXX', file 'XXXXX' on file 1. The step succeeded.

There are about 20 lines like the above. You almost need a language with stronger regular expressions than T-SQL to parse the above, but you could probably make it work. You could substring based on comma position and that would get you pretty far along. Anyway, cool idea - if you take it to completion you might think about writing an article on it.