Failed backup not reported as failed

David SmithDavid Smith Posts: 13
edited July 29, 2007 12:45AM in SQL Backup Previous Versions
I amended a job that runs nightly, to use MIRRORFILE instead of COPYTO because I wanted the self-clean feature of the MIRRORFILE option. Upon checking the job this morning, it had run but there were no new backups for the listed databases. I then cut and copied the script (below) and ran it manually, it reported that the MIRRORFILE option isn't available when specifying multiple databases.

I’m happy with the backup failing because I specified the MIRRORFILE, however the backup failing being considered a successful outcome and neither the MAILTO & MAILTO_ONERROR being honoured not so good.

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASES [db1, db2, db3] TO DISK = ''f:\BAckup\<DATABASE>_<DATETIME yyyymmdd_hhnnss>.sqb'' WITH COMPRESSION = 2, MIRRORFILE = ''\\othersvr\backups\<DATABASE>_<DATETIME yyyymmdd_hhnnss>.sqb'', ERASEFILES_ATSTART = 2, INIT, THREADCOUNT = 2, MAILTO=''sysop@site.com'', MAILTO_ONERROR=''sysadmin@site.com'', VERIFY"'
· Share on Twitter

Comments

  • David SmithDavid Smith Posts: 13
    Some clarification, the job was reported as Failed in the SQL Backup Activity History window, but the SQL Server Agent reported the job as successful.
    · Share on Twitter
  • Jason CookJason Cook Posts: 185
    Hi David,
    If you look in your SQL Backup logs directory (which by default will be the following location, assuming c: is your windows drive), you should find a number of activity logs that correspond to the attempted backups and restores of SQL Backup:

    c:\documents and settings\all users\application data\red gate\sql backup\log\<instance_name>

    If you could find the one that corresponds to the job/backup in question (they are all timestamped) and either post it here or if you'd prefer email it to jason.cook@red-gate.com, I should be able to identify the problem from that.

    Thanks,
    Jason
    · Share on Twitter
  • David SmithDavid Smith Posts: 13
    Cut and paste from the log file(20070726 043000 01.log) that corresponds to the failed backup.


    SQL Backup log file
    26/07/2007 4:30:00 a.m.: Error 516: Multi database operations cannot use MIRRORFILE option.


    David
    · Share on Twitter
  • soonyusoonyu Posts: 8
    I think this is what you want ?

    DECLARE @exitcode int
    DECLARE @sqlerrorcode int
    exec master..sqlbackup N'-SQL "BACKUP DATABASE [Northwind] TO DISK = ''e:\sql\MSSQL\BACKUP\<AUTO>'' WITH NAME = ''<AUTO>'', DESCRIPTION = ''<AUTO>'', COMPRESSION = 1, THREADS = 1"', @exitcode OUTPUT, @sqlerrorcode OUTPUT
    IF (@exitcode <>0) OR (@sqlerrorcode <> 0)
    BEGIN
    RAISERROR ('SQL Backup job failed with exitcode: %d SQL error code: %d', 16, 1, @exitcode, @sqlerrorcode)
    END


    It will appear "fail" in sql history
    · Share on Twitter
  • David SmithDavid Smith Posts: 13
    Not really. What I expected was that the MAILTO and MAILTO_ONERROR to have been honored even though I specified an incorrect option, and the correct status code being returned to sql.
    In this case I would have expected 3 emails, one for each of MAILTO, MAILTO_ONERROR directives and an alert from the SQL Server Agent because the job step exit code indicated the step failed.

    David
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Yes, this is a bug in version 5.x, where the emails are not sent when error 516 is raised. This will be addressed in a future version. Thank you for bringing this to our attention.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • David SmithDavid Smith Posts: 13
    Great, I've been using SQL Backup for less than two months and have run smack bang into two bugs. :D Never mind, hopefully the fixed version will be covered by our license to use 5.1.
    I'll give the @exitcode & @sqlerrorcode example from soonyu a try.

    David
    · Share on Twitter
Sign In or Register to comment.