I am trying get a db restore script to work on our disaster recovery server. Every Sunday morning at 4:30am a backup of our production database is run via scheduled task using the sql statement below:
backup database directory 'B:\DonorCentral\DB\Backup' transaction log rename
The backup and log are then copied over to the disaster recovery (DR) server. Meanwhile, incremental backups run on production four times a day at 6am, 9am, 12pm and 6pm. Those are also copied over to the DR server. Given today is a Monday, we now have a full backup on the DR server from yesterday and five incremental logs (4 from yesterday and one from 6am this morning).
Since the main database on the DR server (not the backup that was copied over) could sit there unused for months and even years, I'm guessing after a little research that I want to restore as if the current db is lost/destroyed. Given that I tried the following based on this post: http://sqlanywhere-forum.sap.com/questions/4760/restoring-incrementallive-backup-failure
"%SQLANY11%\bin32\dbsrv11.exe" -o db_restore_log.txt -oe dbsrv11_log_fatal_temp.txt -os 10M D:\DonorCentral\DB\temp.db -ad B:\DonorCentral\DB\Incremental\20160522 "%SQLANY11%\bin32\dbsrv11.exe" -o db_restore_log.txt -oe dbsrv11_log_fatal_temp.txt -os 10M D:\DonorCentral\DB\temp.db -ad B:\DonorCentral\DB\Incremental\20160523
In this case temp.db is the backup and I am using -ad switch to restore any incremental logs in folders 20160522 and 20160523 to it. Before running this, I copied the backup db to D:\DonorCentral\DB and the backup log to C:\DonorCentral\DB (which is where the log resides). However, when I run the batch script I get the following error:
"cannot open transaction log file -- can't use log file B:\DonorCentral\DB\Incremental\20160522\160522AA.log since it has been used more recently than the database file"
Can anyone shed some light on what the problem might be? Thanks, Tom
There was a bug that 11.0.x could hit that may be at the root of this issue.
Engineering fix #778920 "Under some conditions creating an index can cause invalid entries in transaction log". This was never fixed in the 11.0.x versions. It was fixed in 12.0.1 (builds >= 4233 ) and 16.0.0 (builds >= 2088).
Given the log record is incorrect (with regards to the nature of the primary key) you could try translating the transaction log and apply that as SQL to get past this occurrance.
The bug fix is preventative ... so once you get past this, newer version could prevent it from happening again; which should be, thankfully, pretty rare.
answered 24 May '16, 16:13
Nick Elson S...
The original full backup created a backup *.db and *.log file pair. In your case, the error message is telling you to apply the original *.log backup first because it contains data that isn't contained in the original *.db file.
It is sometimes possible to skip the original *.log file, but it is alway safe to use it first.
This is discussed in this pair of blog posts...
If you think those articles have too many words, this one is even worse: