Litespeed backup in stored procedure

Question

Post reply

Litespeed backup in stored procedure

Jason Bunn

SSCommitted

Points: 1713
More actions
December 19, 2006 at 9:25 am

#153345

Suppose I have the following in a stored procedure:
set @sqlcmd = 'master.dbo.xp_backup_database @database=''' + @p_database + ...
execute sp_executesql @sqlcmd
That is, I'm setting up a string to do a litespeed backup and then executing the resultant T-SQL.
How can I detect if the backup worked? Since the execute is successful, I get nothing in @@error if the backup failed or if it succeeded. How can I examine the output of the backup command itself to see if it failed?
Thanks!
Jason

Viewing 9 posts - 1 through 8 (of 8 total)

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

xp_backup_database takes parameters. Why are you building a string?

EXEC master.dbo.xp_backup_database @database = @p_database,

@filename = @YourFileNameParameter, @threads = @YourThreadsParm....

Eddie Wuerch
MCM: SQL

Jason Bunn SSCommitted Points: 1713 More actions · Answer 2

Point taken! I've never wrapped the xp_backup_database into a stored proc, so I didn't know it takes parameters. Thanks!

Jason Bunn SSCommitted Points: 1713 More actions · Answer 3

Ok, halfway there!

I can now capture a non-zero error code and act on that. Now, is there any way to capture and save in a file the actual Litespeed error message? It would great to see the reasons why it might have failed. In QA, I can see, for instance:

Processed 1 pages for database 'LogShippingSource_JCB', file 'LogShippingSource_JCB_Log' on file 1.

BACKUP LOG successfully processed 1 pages in 0.017 seconds (0.060 MB/sec).

Were it to fail, we can find out why, if we had a way to save this message somewhere when the automated script is executed.

Thanks,

Jason

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

How about the ErrorMessage field of the LitespeedActivity table?

Jason Hall Hall of Fame Points: 3906 More actions · Answer 5

You can also set the @logging parameter equal to 1. This will create a log file in the \litespeed\sql server\engine\logs directory only if the backup fails. If the backup is successful the log file is automatically deleted.

EXEC xp_backup_database @database='northwind', @filename='c:\backup.bak', @logging=1

Hope this helps.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

You can get the error message for the last database backup with the query

SELECT

TOP 1 ErrorMessage

FROM [Litespeed reporting database].dbo.LitespeedActivity

WHERE ActivityTypeID = 1 AND DatabaseID = db_id(@p_database)

ORDER BY ActivityID DESC

That seems a lot easier than trying to import it from a log file.

Jason Bunn SSCommitted Points: 1713 More actions · Answer 7

Thanks for the suggestions!

Scott's suggestion presupposes that we are using the Litespeed activitiy monitor. We haven't done so until now, but this may be a reason to do so. I like the log file idea, but then have to figure out how to detect the log file and act on it.

Is there a more general way of writing the output that usually comes to the QA results pane to a table and then reading the table content?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

Use osql or sqlcmd and save the output to a file.

I haven't tried it, but possibly if you run osql or sqlcmd from xp_cmdshell with no output file command-line option, the output would be returned by xp_cmdshell.

Dynamically generating the xp_cmdshell command with an osql command with an EXEC xp_backup_database command is going to drive you bonkers figuring out all the levels of quotes, but it could be done.