I am using the following code (for example) for backups on the production server, after reading the Sybase documentation. I did not correctly get it, I assume:
BEGIN /* Daily full backup at 10:10am to internal disk skyphos, hu: 3.2.2010 */ BACKUP DATABASE DIRECTORY '/Volumes/skyphos/backups/sqlanywhere/backup10' WAIT BEFORE START WITH CHECKPOINT LOG NO COPY END
I thought that this should be corret, but the problem is that it is sometimes not executed. I assume it has something to do with logged in users while the command is executed.
Since you are using the WAIT BEFORE START clause, it's entirely possible it's just "blocked".
Potentially this clause could block a backup from occurring indefinitely if you are using IdleTimeout=0 or -ti 0 in your server command line arguments (and have users who leave open client applications with uncommitted data when not in the office).
If you are wondering if the backup statement is "blocked" or waiting, it should be simple to confirm. Simply execute "call sa_conn_info()" and see if any of the connections have uncommitted operations (UncommitOps). The backup won't start until each of those connections issues a commit or rollback.
FYI, you may want to consider alternating a backup location between days. In the (hopefully extremely rare) chance that power failure or server failure occurs during the backup, it won't affect the backup and live database.
answered 18 Aug '11, 12:18