We seem to have encountered a problem with dbbackup.exe where it triggers the BackupEnd event (which is confusingly called Backup Completed in Sybase Central) even though the backup fails because it has run out of disk space.

This was originally encountered in ASA 9 but appears to be the case in SQLAny 12 too. The dbbackup.exe tool does correctly return a failure exit code in the command file but I have been unable to get this result in the event.

Is the fact that the event is triggered on failure a bug and/or is there any way for me to tell in the event that it has failed?

Thanks in advance

asked 11 Oct '11, 04:39

Simon's gravatar image

Simon
96449
accept rate: 100%

Um, you are running dbbackup.exe from a command file, why bother with a BackupEnd event at all? I am curious...

(11 Oct '11, 06:04) Breck Carter
Replies hidden

Me, too :)

(11 Oct '11, 06:41) Volker Barth

We use the backupend event to store the date and time of each backup in the database. This allows our application to track backups and notify the users where appropriate.

(11 Oct '11, 07:16) Simon

As the BackupEnd system event is defined as

BackupEnd
You can use the BackupEnd event type to take action at the end of a backup

I would think it is just there to inform you that a backup is finished - successfully or not. There don't seem to be event parameters that show errors or success. But's just my impression...

For better control I would suggest to run the backup as part of an user-defined event (with the BACKUP DATABASE statement instead of using dbbackup.exe). Then you can handle errors by the according SQL statements as you like.

And for ease, there's the maintenance plans facility that let you create such events in a GUI. I have used them as starting points and modify them for my own requirements.

permanent link

answered 11 Oct '11, 05:09

Volker%20Barth's gravatar image

Volker Barth
29.6k294444650
accept rate: 32%

edited 11 Oct '11, 05:32

FWIW... I love events, sometimes even scheduled events, but I would never ever ever ever ever ever (that's 5 ever's, the maximum rating) use an event to run a backup. I care too much about backups, and backup events have too many disadvantages. The suggested alternative is chron / Task Scheduler, plus a command file with however much sophistication you want to code in it, plus dbbackup.exe. In one case the command file starts the database if it isn't running already, just to run a full dbbackup.exe... extreme, yes, but try that with an event :)... but what if the database IS down, what's an event going to do for you? Or if the engine crashes? The list goes on and on...

(11 Oct '11, 05:59) Breck Carter
Replies hidden

Now, that looks like starting a nice discussion...

I have used events for years to backup and validate databases without issues. These are "server" databases running permanently (though typically not with active transactions during full nightly backups). So the databases are running unless some kind of system maintenance takes place - and during that, I'm fine with no automatic backups (though the events may backup logs even then) as I will do the necessary backups (for upgrades etc.) step by step.

I think it's easier to check a database table whether all maintenance has taken place as looking in console logs or the like. In case something unexpected happens, well then logs will help to find out more...

Additionally, the events are typically blocked if some kind of "system disturbance" would appear - in which case I would not want to make further backups but rely on the existing ones (with more than one generation, of course).

How will your dbbackup process handle a backup of a database running on a crashed engine? Will it be able to connect to the database at all? And do you want a backup of that state at all?

(11 Oct '11, 06:40) Volker Barth

Thanks for the replies.

I've looked at the possiblility of backing up through events in the database but we have never done it that way and arent sure its the way to go. Firstly we like to have the option of running the backup on a another machine that writes locally and connects over the network. Secondly its a lot easier for our customers to change a scheduled task and/or batch file than change a backup event.

If the database itself has crashed and the backup fails, thats fine. For starters our entire application would fail and they would restart. Our application will act on the lack of an entry and warn the users. The problem is that the backup failed, the event fired and our application thinks a backup took place. This only seems to happen with a disk space error.

Either the event should not fire (and perhaps they should not name the event "Backup Completed") or the event could do with a parameter with the result.

(11 Oct '11, 07:30) Simon

Well, it isn't named "BackupCompleted" - possibly for a reason...

I would suggest to add your wish for a "success event parameter" as a product suggestion - adding this to your original question should be fine, methinks. There's a tag "product-suggestion" as well.

W.r.t. backup events and the need of client-side backups: Note that both can be done to the same database (though not concurrently). - That's what we do, too...

(11 Oct '11, 08:00) Volker Barth
1

If the dbbackup utility is run from a shell script you might want to check for success and use ISQL (or another tool) to insert an appropriate record into your backup history table. So you won't have to rely on the BackupEnd event.

(11 Oct '11, 08:12) Reimer Pods

It is called "Backup completed" in Sybase Central. I may try to open this as an enhancement request. Is this forum the best place to do it?

(11 Oct '11, 08:52) Simon

This was my line of thinking. Its the closest I've come to an acceptable solution.

(11 Oct '11, 08:53) Simon
1

Ah, thanks for the SC pointer - I use the event name as documented (yes, I prefer scripts...).

I can't tell whether this forum is the "best place" for such requests (as I'm no Sybase employee) - but at least it's one of the places monitored by Sybase product managers...

The other one I'm aware of is the particular newgroup "sybase.public.sqlanywhere.product_futures_discussion".

(11 Oct '11, 09:09) Volker Barth
1

Well, if events are not the way to go but you think about using a SQL script to add the backup success to your database, wouldn't it be easier to use the ISQL session (or any query tool) to run

  • both a BACKUP DATABASE statement
  • and the statements to insert/update the backup success

in the same script?

In my book, that's still easier to handle (and allows better error handling) than a combined DBBACKUP/ISQL approach. And if the backup script has to be customizable, that's doable with a SQL script as well as with a shell script/command batch IMHO.

Further, more "programmed" approaches might be

Just my 2 cents...

(11 Oct '11, 16:48) Volker Barth

Thanks for the suggestions. I think you are probably right about consolidating the backup and the success check. Will look into this.

The DBTools API looks interesting but it looks like more work to set up than we would like. Unless there is an simple way to do it in VB.NET?

(14 Oct '11, 03:52) Simon

I would think calling a native C API from VB.NET is not that easy - but I have never tried this before...

(14 Oct '11, 04:52) Volker Barth
1

DBTools applications must be written in C/C++, but if you can write the DBTools functionality into a .DLL, you can certainly call it from VB.NET via DLLImport.

(14 Oct '11, 11:47) Jeff Albion
showing 1 of 12 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×84
×71
×35

question asked: 11 Oct '11, 04:39

question was seen: 1,766 times

last updated: 14 Oct '11, 11:47