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.

asked 14 Aug '11, 07:24

robert's gravatar image

robert
578293848
accept rate: 0%

edited 14 Aug '11, 08:12

Graeme%20Perrow's gravatar image

Graeme Perrow
8.6k373110

I should correct my above saying: The (backup) event IS executed, but the db file is empty, i. e. 0 k, where the log file looks ok, i. e. has a size greater than 0 k. Sorry and thanks, Robert

(14 Aug '11, 18:07) robert
Replies hidden

I'd try to run the backup without the WAIT BEFORE clause and check whether this changes the behaviour. Adding MESSAGE TO CONSOLE statements before and after the BACKUP statement will help, too...

(15 Aug '11, 15:36) Volker Barth

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.

permanent link

answered 18 Aug '11, 12:18

Tyson%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

1

I would strongly recommend this too - with a single backup location you run the same but real risk of losing both your live and backup databases as a result of an ill-timed incident. Hopefully you would have older backups off-line / off site, but if you have the space available it is a really good idea to rotate locations at least on alternate days.

(21 Aug '11, 06:42) Justin Willey
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:

×72

question asked: 14 Aug '11, 07:24

question was seen: 612 times

last updated: 21 Aug '11, 06:42