Question Re: Testing backUp SQL Anywhere 10 in Technology Q&A

Testing backUp SQL Anywhere 10

Breck_Carter — Mon, 12 Apr 2010 10:31:03 GMT

From: "Claude"

Newsgroups: sybase.public.sqlanywhere.general

Subject: BackUp SYB 10

Date: 9 Apr 2010 23:49:23 -0700

I used to do the backup of SYB 10 with DBBAckup, but after stopping the DB ( script netstop db, dbbackup, netstart db )

I am currently testing the "warm" backup, ie dbbackup without stopping the DB.

I am supprised to see that , if I run this backup now, I find the db and log files with dates in the past, and different for db and log. The DB date can be dated 2 days ago, and the log 1 day ago. Is this normal, How can I be sure that this backup is correct, and includes the latest transacations done?

Claude

Re: Testing backUp SQL Anywhere 10

Breck_Carter — Mon, 12 Apr 2010 10:48:29 GMT

I used to do the backup of SYB 10 with DBBAckup, but after stopping the DB ( script netstop db, dbbackup, netstart db )

What did the connection string look like on the dbbackup command line? Did it use a DSN with autostart specified?

The reason I ask is that the dbbackup.exe only does warm backups; i.e., it only connects to a running database, so in order for it to work on a stopped database it would have to autostart it... then, if the DSN also specified autostop, the database engine would stop when dbbackup was finished thus allowing the netstart to work.

If that's not the explanation, then I certainly want to hear about it! ( I can feel the ground shifting 'neath my feet 🙂

The OS file system datetime for the *.db and *.log files is not changed every time data is written to the files. I can't remember what exactly the rules are, but AFAIK if the files change in size then the datetime is updated. Variations in the file datetimes are very common and of no concern... do not rely on those datetimes for anything.

How can I be sure that this backup is correct, and includes the latest transacations done?

Dmitri answered "trust it" in the newsgroup... and initially I thought that was too glib but I'm having a hard time coming up with a better answer, such as a test you could perform after every backup.

( in practice, glib or not, Dmitri's answer works for me 🙂

Anyway, here's one possibility, probably best suited for a one-time test: Create a test table and insert a row with COMMIT just before doing the backup. After the backup is complete, start the backup database using the backup log, in the backup folder; specify a different -n server name on the dbeng10 command line to avoid conflicts. The database start process will go through normal "recovery" to apply the log to the database. Then, do a query to make sure the test row is there.

You could do a test with two inserted rows, one committed and the second one not. After the backup database is started, only the first row should be there because the second row was not committed... that is expected behavior: the original connection that did the uncommitted insert does not exist on the backup database so the intent (commit or rollback) is unknown, therefore rollback must be assumed.

Re: Testing backUp SQL Anywhere 10

former_SQLA_member1694880 — Mon, 12 Apr 2010 14:08:49 GMT

TBH I neither have reason to distrust DBBACKUP nor I have good idea how to test backup and running databases for equality (I know that database backup is more complex then file copy :)).

AFAIK .DB and .LOG files timestamps change when they are extended and on database shutdown.

Re: Testing backUp SQL Anywhere 10

johnsmirnios — Mon, 12 Apr 2010 15:39:37 GMT

Which db & log file have old dates: the backup files or the ones that are currently in use by the server? I would expect the former (ie the backup files) to have a current timestamp and the latter could, as Breck has explained, appear to have old timestamps.