SQLBkup 32 - Making Last Run Status Failure on backup fail

Bern AndersonBern Anderson Posts: 27
edited May 26, 2005 9:09AM in SQL Backup Previous Versions
Brian - I saw this topic from the "Knowledge Base" and was wondering if there was a good way of doing the same thing from the command line. I have a batch file which contains individual commands such as the following:

"C:\Program Files\Red Gate\SQL Backup\SqlBackupC.exe" -USE "dwdev01_user_db_full_fin_mgmt_hum_res_sup_stg"

and would like to know when a database backup fails. The batch file is executed from a third party job scheduler.

On another related topic, in the GUI options there is a place to have an email sent at the completion of the backup job; however, I only want an email when it fails. I don't want to be spammed by 'successes'. Is there a way of accomplishing this. Also, can the email addressee accept environment variables? It complains unless there is a normally formatted email address.

Thanks,

- Bern

Comments

  • peteypetey Posts: 2,358 New member
    SQL Backup returns an exit code equal to it's error code (as found in the SQL Backup help file). A value of 0 indicates success. You can use the following batch file to try it:

    @ECHO OFF
    e:\temp\_sqlspeedbak\sqlbackupc -sql "backup database pubs to disk = 'c:\temp\pubs.sqb' with init"

    IF %ERRORLEVEL% == 0 GOTO success
    echo Failed with exit code %ERRORLEVEL%
    GOTO end

    :success
    echo Successful
    GOTO end

    :end

    The GUI does not have the option to send an email only upon failure. However, it can be done if you change the script to use MAILTO_ONERROR instead of MAILTO.

    What sort of environment variables are you referring to?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Peter,

    I have a windows environment variable called "irm_mssql_dbas" that contains one or more email addresses for the relevent dba(s) for the server. I want to have the results mailed to the dbas listed in the environment variable so I don't have to define them specifically for each backup set that I create.

    Also, can I edit the command line code produced by the BU wizard without going through the GUI interface? I'd really like to use a text editor to create additional backup sets, or modify existing ones, without having to go through the BU wizard in order to save time.

    Thanks,
    - Bern
  • peteypetey Posts: 2,358 New member
    Interesting suggestion.

    You can just edit the scripts to fit your needs and use them in the extended stored procedure or command line interfaces. The scripts in the GUI are there as a guide, to show how to 'script' each option. However, some of the options are not exposed in the GUI e.g. the LOGTO and ERASEFILESOPTION options, in order to keep things simple.

    The help file contains full details of the valid syntax.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    You probably also want to add to your backup script:
    MAILTO=''%irm_mssql_dbas%''

    This will work if you have more than one email address in the variable, but only if they're separated by commas!
Sign In or Register to comment.