Restoring incremental/live backup failure

[Former Member]

The idea was to create remote incremental backups after a full backup and then restore in the event of a failure and bring up another machine with as close to real time backups as possible with SQL Anywhere network servers.

Doing a full backup as follows:

dbbackup -y -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" "c:\\backuppath\\full"

This grabs the db and log file and can be restored as expected and works fine. For incremental backups I've tried both live and transaction logs with the following and both initially seem to work or at least do something:

dbbackup -y -t -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" "c:\\backuppath"

dbbackup -y -l "c:\\backuppath\\live.log" -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" "c:\\backuppath"

However on applying the transaction logs on restore I always receive the error "10092: Unable to find table definition for table referenced in transaction log" when applying the transaction logs to the database like so:

dbeng11 "c:\\dbpath\\dbname.db" -a "c:\\backuppath\\dbname.log"

The error doesn't specify what table it can't find but this is a controlled test and no tables are being created or dropped. I insert a few rows then kick off an incremental backup before attempting to restore.

Does anyone know the correct way to do incremental backup and restore on Sql Anywhere 11?

UPDATE: Thinking it may be related to the complexity of the target database I made a new blank database and network service. Then added one table with two columns and inserted a few rows. Made a full backup, then inserted and deleted a few more rows and committed transactions, then made an incremental backup. This also failed with the same error when attempting to apply the incremental backups of transaction logs after restoring the full backup ... what step am I missing?

UPDATE2: I got a few successful restores using the following on the remote machine yesterday but still nothing consistently reliable enough to consider it a solution. More often than not I still have issues related to applying the transaction log on the restore. I will try something more along the lines of the script posted as an answer below. Ideally using dbbackup with the -l "live" would be perfect but that has never worked successfully for me yet.

Full Backup:

dbbackup -y -c "uid=dba;pwd=sql;eng=ServerName.Demo;links=tcpip(host=ServerName)" backup
dbvalid -c "uid=dba;pwd=sql;dbf=backup\\database.db" > validate.log

Transaction Log Backup: (Could run every 15 minutes?)

del log\\database.log /f /q
dbbackup -y -t -c "uid=dba;pwd=sql;eng=ServerName.Demo;links=tcpip(host=ServerName)" log

Restore From Backup: (Works 25% of the time)

copy /y backup\\*.* demo\\*.*
dbeng11 demo\\database.db -ar
dbeng11 demo\\database.db -a log\\database.log
del demo\\database.log /f /q
copy /y log\\database.log demo\\database.log
dbeng11 demo\\database.db -ar

UPDATE3: Turns out I get the same errors whether using dbeng11 or dbsrv11. This is the latest attempt at getting a live backup of transaction log to work.

@ECHO OFF

cd c:\\db

dbbackup -o fullbackup.log -y -c "uid=dba;pwd=sql;eng=ServerName.Demo;links=tcpip(host=ServerName)" backup
dbvalid -o validatedb.log -c "uid=dba;pwd=sql;dbf=backup\\database.db"

dbbackup -o livebackup.log -y -l log\\database.log -c "uid=dba;pwd=sql;eng=ServerName.Demo;links=tcpip(host=ServerName)" log
dbtran -o validatelog.log log\\database.log log\\database.sql

md Demo
copy /y backup\\*.* demo\\*.*

dbsrv11 -o restore.log -oe rerr.log demo\\database.db -ar
del demo\\database.log /f /q
copy /y log\\*.log demo\\*.*
dbsrv11 -o restore.log -oe rerr.log demo\\database.db -ar -as
dbvalid -o validatedb.log -c "uid=dba;pwd=sql;dbf=demo\\database.db"

[Breck_Carter]

Incremental log backups and live log backups are two entirely different techniques.

Here is a script for an incremental log backup. It assumes the current database and log files exist in the current folder, and the most recent full database and log backups reside in the subfolder bkup\\generation10 (see Rotating Database Backups Revisited).

ECHO ********************************************^
*********************************************>>bkup\\dbbackup_log.txt
ECHO Incremental dbbackup started >>bkup\\dbbackup_log.txt
DATE /T >>bkup\\dbbackup_log.txt
TIME /T >>bkup\\dbbackup_log.txt

CD bkup\\generation10
MD logs
CD ..\\..

REM Backup to generation10 subfolder
"%SQLANY11%\\bin32\\dbbackup.exe"^
  -c "ENG=ddd11;DBN=ddd11;UID=dba;PWD=sql"^
  -o bkup\\dbbackup_log.txt^
  -n^
  -t^
  -x^
  bkup\\generation10\\logs
IF ERRORLEVEL 1 GOTO ERR_BACKUP

REM Backup OK
DATE /T >>bkup\\dbbackup_log.txt
TIME /T >>bkup\\dbbackup_log.txt
DIR /S bkup\\*.* >>bkup\\dbbackup_log.txt
ECHO Incremental dbbackup OK >>bkup\\dbbackup_log.txt
DATE /T >>bkup\\dbbackup_log.txt
TIME /T >>bkup\\dbbackup_log.txt
GOTO END

:ERR_BACKUP
REM Backup failed
DATE /T >>bkup\\dbbackup_log.txt
TIME /T >>bkup\\dbbackup_log.txt
DIR /S bkup\\*.* >>bkup\\dbbackup_log.txt
ECHO Error: Incremental dbbackup failed >>bkup\\dbbackup_log.txt
GOTO END

:END

If the current database and log files are lost/destroyed, here are the commands that can be used to restore the full backup database file, and then apply the incremental logs. Note that in this recovery, the original full backup log file plays no role (it is not needed).

COPY bkup\\generation10\\ddd11.db

REM Note: -o writes to the backup diagnostic file.

"%SQLANY11%\\bin32\\dbsrv11.exe"^
  -o bkup\\dbbackup_log.txt^
  -oe dbsrv11_log_fatal_ddd11.txt^
  -os 10M^
  ddd11.db^
  -ad bkup\\generation10\\logs

Here's what the server diagnostics look like when the -ad option is used...

I. 03/25 10:05:49. SQL Anywhere Network Server Version 11.0.1.2558 I. 03/25 10:05:49. Workgroup edition
...
I. 03/25 10:05:49. Starting database "ddd11" (C:\\projects\\$SA_templates\\run\\dbbackup\\demo_restore_incremental_backup11\\ddd11.db) at Fri Mar 25 2011 10:05
I. 03/25 10:05:49. Database recovery in progress
I. 03/25 10:05:49.     Last checkpoint at Fri Mar 25 2011 10:02
I. 03/25 10:05:49.     Checkpoint log...
I. 03/25 10:05:49.     Transaction log: C:\\projects\\$SA_templates\\run\\dbbackup\\demo_restore_incremental_backup11\\bkup\\generation10\\logs\\110325AA.log...
I. 03/25 10:05:49. Starting checkpoint of "ddd11" (ddd11.db) at Fri Mar 25 2011 10:05
I. 03/25 10:05:50. Finished checkpoint of "ddd11" (ddd11.db) at Fri Mar 25 2011 10:05
I. 03/25 10:05:50.     Transaction log: C:\\projects\\$SA_templates\\run\\dbbackup\\demo_restore_incremental_backup11\\bkup\\generation10\\logs\\110325AB.log...
I. 03/25 10:05:50. Starting checkpoint of "ddd11" (ddd11.db) at Fri Mar 25 2011 10:05
I. 03/25 10:05:50. Finished checkpoint of "ddd11" (ddd11.db) at Fri Mar 25 2011 10:05
I. 03/25 10:05:50.     Transaction log: C:\\projects\\$SA_templates\\run\\dbbackup\\demo_restore_incremental_backup11\\bkup\\generation10\\logs\\110325AC.log...
I. 03/25 10:05:51.     Checkpointing...
I. 03/25 10:05:51. Starting checkpoint of "ddd11" (ddd11.db) at Fri Mar 25 2011 10:05
I. 03/25 10:05:51. Finished checkpoint of "ddd11" (ddd11.db) at Fri Mar 25 2011 10:05
I. 03/25 10:05:51. Recovery complete
I. 03/25 10:05:51. Database server shutdown automatically after log applied
I. 03/25 10:05:51. Database server stopped at Fri Mar 25 2011 10:05

I have tested these commands in version 11 (shown above) and version 12 (no significant differences), and I may post an article on my blog (any requests?).

In the meantime... If you mess around with the incremental dbbackup -n -t -x options, good luck making it work 🙂 The setup shown here puts the incremental logs in a different subfolder, and it neither overwrites any files nor creates any duplicate files.

[Former Member]

So you are using personal server to restore and quite different parameters for backups. This seems more complete then what I was attempting I will give it a try. I got it working a few times but not consistently. Do you have any experience with using a "live" backup from dbbackup as that fails 100% of the time for me no matter what I try.

[VolkerBarth]

You should generally use that type of engine (i.e. dbengX vs. dbsrvX) to restore that has made the backup, too. AFAIK that has to do with some operations that do require different permissions on the personal vs. network servers. In case these operations have to be recovered, it's ecessary (or just better) to run the same engine that logged these operations.

[Breck_Carter]

@AJG85: Where do you see the personal server in use?

[Breck_Carter]

@AJG85: Yes, I have used live backup successfully to provide "warm standby" on a different computer, in different buildings and different continents. First, you create a full backup of the database and ship it to the remote computer. Then, you start running dbbackup -l on the remote computer, connected back to the primary server. It runs continuously, and log records are shipped to the remote computer and written to the log backup file. When a failure occurs, you start the backup database on the remote computer and apply the backup log file. Like I said, it is a different technique, and rather funky... like mirroring, but older.

[Breck_Carter]

@Volker: AFAIK the main reason to use dbsrv11 to restore if you've been running dbsrv11 regularly, is that the network engine can have more connections... and all those connections may be required to apply all the transaction log entries. I am having trouble finding where that is documented, however.

[Former Member]

Oops you're right I had it backwards. My use of dbeng11 may be part of the problem as I am backing up a network server running with dbsrv11. I will switch this in case as Volker pointed out additional connections or permission differences are causing operations to be missing from the backup.

[VolkerBarth]

AFAIK, you would have to do the following:

1. Copy the current db and log to a secure location.
2. Copy the full backup files to the current location.
3. Start them with dbeng11/dbsrv11 -a. This should apply the log of the full backup and shut down.
4. Copy the newest backup log to the current location.
5. Repeat step 3.

Now your database should be fully recovered.

(If you rename the logs during backup, you could put both logs in the database loaction and apply both with -ar.)

[Former Member]

If you have to copy the current db and log what's the point? The idea is you won't have access to that in a failure and need to restore from the last full backup and live log that you do have available. I will try the -ar switch as I am changing the name of the log file in most cases.

[VolkerBarth]

Well, I would always copy any production file to a secure location before doing a restore (and would always copy the backup before using it to restore, too). It's just a simple step to prevent against manual failures - which are not rare in recovery...

Of course, if the production database files are unavailable (say, because of a hardware crash), well, then this step would be unnecessary:)

[Former Member]

I tried copying the full backup to another folder before restoring with the -ar switch then applied the live transaction log with the -a switch after ... no errors. However when i created a new service and loaded this up the changes were not there. I guess this is "progress" ... is there a utility or way of checking what is actually in the transaction log backup or a way of validating the backups?

[VolkerBarth]

Are you sure your changes have been committed before the log backup was taken? - You can easily find out about the log contents by using the DBTRAN utility. It is able to translate the contents in SQL statements. And there are several ways of validating databases and logs. By definition, a log that can be translated by DBTRAN is a valid one.

[Former Member]

Unless there is a i/o issue or delay yes as I'm doing a very controlled test where I actually close down and stop all services and connections to the database server before running the backups remotely. I got it to succeed a few times yesterday but never consistently there seems to be some kind of corruption taking place when backup up the transaction log. I wouldn't call myself a DBA by any means and my previous db experience is with SQL Server not Sybase so that is large part of the problem most likely ...

[VolkerBarth]

To help further, it might be helpful if you could show which files you have before and after a backup. Have you tried to use DBTRAN to check for the log contents? Most SQL Anywhere installations don't require any DBA, so in general, you should not need to be one to create a successful backup:)

[Former Member]

DBTRAN spits out a sql file which looks good to me and as someone mentioned previously if it's able to parse it then it's probably valid. I've thought of possibly just running these SQL statements against the full backup to recreate the changes since restoring from the transaction log doesn't consistently work.

[former_SQLA_member1694874]

We've had problems before where if a transaction is started but not commited, a log is created, then once DBTRAN is used and the sql is applied, because there is no commit for the transaction in that log it's rolled back on the backup server and the next log (once the transaction has been committed) doesn't include the transaction. I think that's one of the reasons why using DBTRAN as a backup strategy isn't recommended by sybase. While it works a lot of the time as a live backup, it's not perfect.

[VolkerBarth]

IMHO, DBTRAN is not a backup tool. A recovery with a regular backup should work - point. If it doesn't, well, then probably the backup plan (or the recovery plan) is wrong. Then it should be fixed.

Just in the undsired case a regular backup doesn't work, then DBTRAN might help to get the missing transactions back. But that will usually require much more knowledge than applying a backup.

That being said, DBTRAN is a very valuable tool to find out how operations are logged - and whether a transaction log is valid.

[Breck_Carter]

No, no, a thousand times no... the output from dbtran is not sufficient for the purposes of restoring a database in the general case. For one thing, SQL operations from different connections (different transactions) are intermixed in the dbtran file, some of which may have been rolled back and others committed when originally executed. In special cases, when other methods fail, the output from dbtran is useful... but it can require a great deal of intensive inspection and manual effort and is fraught with error. In my experience the dbtran output is useful for recovering from human error; e.g., recreating a stored procedure that was accidentally DROPped, or re-inserting rows that were accidentally DELETEd, when nothing else is wrong with the database (nothing requiring an actual restore).

[VolkerBarth]

Agreed (a thousand times) - that's what I tried to explain...

This leads to the simple conclusion that a backup should be done with the official backup tools, i.e. the DBBACKUP utility, the BACKUP statement and the like.

Lots of folks do use these tools and rely on their effectiveness when protecting their productive systems.

So, if they don't work as expected during your tests, they are used in a somewhat incorrect way, and that should be fixed.

[Breck_Carter]

See Demonstrating Full and Incremental Backups