Backup Database

[former_SQLA_member1694872]

How do I know if the database is not presenting problem, so that does not generate a backup of a database invalid (with db error)?

[Breck_Carter · Accepted solution]

After creating a backup, start the backup in read-only mode and run the dbvalid.exe utility against it. If dbvalid does not report any errors, then you know that both the backup and the original database are OK.

[former_SQLA_member1694872 · Accepted solution]

How do I run dbvalid through an event database? Its create an event to perform validation of the database automatically?

[Breck_Carter · Accepted solution]

You could use xp_cmdshell to launch dbvalid.exe from inside an event, but I suggest using the Windows - Accessories - System Tools - Task Scheduler to launch a *.bat file because it's easier to administer and track.

...or a chron job in Linux.

[VolkerBarth]

While Breck's statement is the official correct one, in case your database is not actively used 24x7 (*), you may also validate the existing database in a "quiet" timespan and then make a backup.

Theoretically, that's a bit more error-prone (the database could become invalid between validation and backup, or the backup itself could fail), but usually the statements in use should notify you accordingly with error messages - and for failing backup processes, you should always have at least one previous "safe" backup.

You may use the Sybase Central Maintenance plan facility to generate such events, if you prefer a GUI-based approach.

(*) Note: Validating a database with active transactions may lead to false positives and is not recommended...

[former_SQLA_member1694872]

Imagine the following scenario. With the main database started, I created an event within that db that does the following process:

IF NOT exists (select null from sysevent where SYSEVENT.EVENT_NAME = 'TEST') then
CREATE TEST EVENT
SCHEDULE daily_TESTE
START TIME '11: 00AM '
ON ('Mon')
START DATE '2012-11-28 '
HANDLER
   BEGIN
      START DATABASE 'c: \\ system \\ BACKUP \\ BACKUP_wed_11 \\ DBINTELIG.db' ON THE BDBACKUP DBINTELIG
    CALL sa_validate (owner_name = 'DBA') --- in the database BDBACKUP
    STOP DATABASE BDBACKUP

   END;

END IF;

Work?

[VolkerBarth]

That won't work, as the sa_validate() call relates to the current database of the current connection, and not to the freshly started one.

If you want to valid the backup via an event triggered from your production database, it would be way easier to use xp_cmdshell() to call the DBVALID tool to start and validate the backup in one command, as Breck has sugested.

Why do you want to use an event at all? Does it do the backup before? (Don't get me wrong, events are fine, but for scheduled file-related tasks, the OS scheduler might be easier to setup and monitor, as Breck has pointed out.)