SQL Anywhere v. 11.0.1. 2713 - validate - backup

[Former Member]

Hi all,

We are using SQL Anywhere 11.0.1. 2713 on various customer sites. We have been happy (and still are) with the stability of the database.

We have a few questions because we want to implement some improvements.

From time to time we experience either an assertion error , or not being able to validate the database anymore. At this moment we are taking the backups and applying the log file to it , and this works most of the time.

Our backup - statement is a db event : BACKUP DATABASE DIRECTORY 'c:\backups\monday\' TRANSACTION LOG TRUNCATE

We scheduled this event for every customer.

We would want to enhance this event like this : "only start the backup action, when the dbvalidate is ok".

Is it possible to have the dbvalidate option/statement inside the db event and have a succesfull return code for the validation ?

If yes, how please ?

TIA

John

[Former Member]

Hi John,

I've pasted a backup event from SQL Anywhere that is based on the sample backup event in the demo database. It includes validating and then backing up the database.

Another option is to create a scheduled validation event that as a final step calls the existing backup event which has been changed to a manual event.

The email section is optional and it can just write results to the database server verbosity log (dbsrv11 -o filename.txt)

Thanks

Mark

CREATE EVENT "DBA"."DB_Valid_Backup"

SCHEDULE "DB_Valid_Backup_schedule" START TIME '01:00' ON ( 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday' ) START DATE '2015-01-01'

HANDLER

BEGIN

    DECLARE @SUCCESS            BIT;

    DECLARE @START_TIME         TIMESTAMP;

    DECLARE @REPORT             LONG VARCHAR;

    DECLARE @MSG                LONG VARCHAR;

    DECLARE @ERROR_MSG          LONG VARCHAR;

    DECLARE @ERROR_STATE        LONG VARCHAR;

    DECLARE @ERROR_CODE         INT;

    DECLARE @dest               LONG VARCHAR;

    DECLARE @day_name           CHAR(20);

    SET @SUCCESS = 1;

    SET @START_TIME = CURRENT_TIMESTAMP;

    SET @REPORT = NULL;

    SET @day_name = DATENAME( WEEKDAY, CURRENT DATE );

    SET @dest = 'c:\\backups\\CDB\\' || @day_name;

    // Beginning of report message

    SET @MSG = 'Backup plan ' || EVENT_PARAMETER( 'EventNAme' ) || ' for ' || DB_PROPERTY( 'Name' ) || ' on ' || PROPERTY( 'Name' ) || ' started on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';

    SET @REPORT = @REPORT || @MSG || '\n';

    MESSAGE @MSG;

    // Check for active event instances

    IF EVENT_PARAMETER( 'NumActive' ) > 1 THEN

        SET @MSG = 'Backup plan ' || EVENT_PARAMETER( 'EventNAme' ) || ' for ' || DB_PROPERTY( 'Name' ) || ' on ' || PROPERTY( 'Name' ) || ' is already running';

        SET @REPORT = @REPORT || @MSG || '\n';

    MESSAGE @MSG;

    ELSE

        BEGIN

            // Validation

            SET @MSG = 'Database validation started on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';

            SET @REPORT = @REPORT || @MSG || '\n';

            SET @MSG = 'Validating database pages';

            SET @REPORT = @REPORT || @MSG || '\n';

            VALIDATE DATABASE;

            SET @MSG = 'Database validation finished on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';

            SET @REPORT = @REPORT || @MSG || '\n';

            MESSAGE @MSG;

            // Backup

            SET @MSG = 'Database Backup started on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';

            SET @REPORT = @REPORT || @MSG || '\n';

            SET @MSG = 'Backing up to image: '|| @dest;

            SET @REPORT = @REPORT || @MSG || '\n';

                BACKUP DATABASE DIRECTORY @dest

//               WAIT BEFORE START                -- causes blocks. Understand its use before using

                TRANSACTION LOG TRUNCATE

                TRANSACTION LOG RENAME;

            SET @MSG = 'Database backup finished on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';

            SET @REPORT = @REPORT || @MSG || '\n';

            MESSAGE @MSG;

            EXCEPTION WHEN OTHERS THEN

                SELECT ERRORMSG(), SQLSTATE, SQLCODE INTO @ERROR_MSG, @ERROR_STATE, @ERROR_CODE;

                SET @SUCCESS = 0;

                SET @MSG = 'The Backup plan has ended because of the following error:\n'

                    || @ERROR_MSG || '\nSQLSTATE: ' || @ERROR_STATE || '\nSQLCODE:  ' || @ERROR_CODE;

                SET @REPORT = @REPORT || @MSG || '\n';

            MESSAGE @MSG;

        END;

    END IF;

    // End of report message

    SET @MSG = 'Backup plan ' || EVENT_PARAMETER( 'EventNAme' ) || ' for ' || DB_PROPERTY( 'Name' ) || ' on ' || PROPERTY( 'Name' ) || ' finished on ' || CURRENT DATE || ' at ' || CURRENT TIME || '';

    SET @REPORT = @REPORT || @MSG || '\n';

    MESSAGE @MSG;

    // Email report

    email:BEGIN

        DECLARE @SMTP_SENDER                LONG VARCHAR;

        DECLARE @SMTP_SERVER                LONG VARCHAR;

        DECLARE @SMTP_SENDER_NAME           LONG VARCHAR;

        DECLARE @RECIPIENTS                 LONG VARCHAR;

        DECLARE @MAIL_SUBJECT               LONG VARCHAR;

        DECLARE @SMTP_RETURN_CODE           INT;

        SET @SMTP_SENDER = 'name@domain.com';

        SET @SMTP_SERVER = 'smtp.servername.com';

        SET @SMTP_SENDER_NAME = 'SUP Administrator';

        SET @RECIPIENTS = 'name@domain.com';

        IF @SUCCESS = 1 THEN

            SET @MAIL_SUBJECT = 'Successful: Backup plan ' || EVENT_PARAMETER( 'EventNAme' ) || ' for ' || DB_PROPERTY( 'Name' ) || ' on ' || PROPERTY( 'Name' );

        ELSE

            SET @MAIL_SUBJECT = 'Failed: Backup plan ' || EVENT_PARAMETER( 'EventNAme' ) || ' for ' || DB_PROPERTY( 'Name' ) || ' on ' || PROPERTY( 'Name' );

        END IF;

        @SMTP_RETURN_CODE = CALL dbo.xp_startsmtp( smtp_sender = @SMTP_SENDER, smtp_server = @SMTP_SERVER, smtp_sender_name = @SMTP_SENDER_NAME );

        IF @SMTP_RETURN_CODE <> 0 THEN

            SET @MSG = 'dbo.xp_startsmtp() failed: (return code = ' || @SMTP_RETURN_CODE || ')';

            SET @REPORT = @REPORT || @MSG || '\n';

                        MESSAGE @MSG;

            LEAVE email

        END IF;

        @SMTP_RETURN_CODE = CALL dbo.xp_sendmail( recipient = @RECIPIENTS, subject = @MAIL_SUBJECT, "message" = @REPORT );

        IF @SMTP_RETURN_CODE <> 0 THEN

            SET @MSG = 'dbo.xp_sendmail() failed: (return code = ' || @SMTP_RETURN_CODE || ')';

            SET @REPORT = @REPORT || @MSG || '\n';

                        MESSAGE @MSG;

        END IF;

        @SMTP_RETURN_CODE = CALL dbo.xp_stopsmtp();

        IF @SMTP_RETURN_CODE <> 0 THEN

            SET @MSG = 'dbo.xp_stopsmtp() failed: (return code = ' || @SMTP_RETURN_CODE || ')';

            SET @REPORT = @REPORT || @MSG || '\n';

                        MESSAGE @MSG;

        END IF;

    END;

END;