|
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. |
|
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... Quiz: Which log backups are required to restore a database? Answer: Which log backups are required to restore a database? If you think those articles have too many words, this one is even worse: So just doing this: "%SQLANY11%\bin32\dbsrv11.exe" -o db_restore_log.txt D:\DonorCentral\DB\temp.db -a C:\DonorCentral\DB\temp.log where temp.db and temp.log is from the Sunday online backup should do the trick?
(23 May '16, 17:20)
Tom Rolseth
Replies hidden
Yes. That should be the initial recovery step. FWIW: You should always use copies of the backup files when doing a recovery (or having some more copies of the backup files) to protect against media and/or user errors when doing the recovery steps...
(23 May '16, 17:41)
Volker Barth
I tried this and got past the 'cannot open transaction log file' error. It ran for a while and seemed to apply the incremental logs but then failed with 'Assertion failed: 100904, failed to redo a database operation'.
(23 May '16, 17:45)
Tom Rolseth
Replies hidden
here is the crash dump report info: VERSION=11.0.1.3158 FILENAME=C:\ProgramData\SQL Anywhere 11\diagnostics\SA11_20160523_172842_6592.crash_log OS=Windows 2012 Build 9200 PROCESSOR=X86_64 EXEC_ARCH=X86 EXEC_PATH=C:\Program Files\SQL Anywhere 11\bin32\dbsrv11.exe MODULE_PATH=C:\Program Files\SQL Anywhere 11\bin32\dbserv11.dll EXCEPTION_PTR=0A08F04C EXCEPTION_CODE=2147483651 EXCEPTION_FLAGS=0 EXCEPTION_RECORD=00000000 EXCEPTION_ADDRESS=753A5B68 EXCEPTION_NumParameters=1 EXCEPTION_Param0=00A84EA4 TRYING_TO_SAVE_MINI_DUMP C:\ProgramData\SQL Anywhere 11\diagnostics\SA11_20160523_172842_6592.dmp DUMPLEVEL 0 SAVING_MINI_DUMP_COMPLETED CRASH_LOG_COMPLETE ATTEMPTING_TO_SUBMIT_MINIDUMP...
(23 May '16, 17:49)
Tom Rolseth
Is there any further error message text? - The forum lists a few of these assertion cases, and they had more info. Do you have a newer 11.0.1 EBF to try to do the restore with that? Do you know which incremental log was applied at that time?
(23 May '16, 17:57)
Volker Barth
the assertion error occurred after a bunch of checkpoints while reading the first incremental log from yesterday. Here is the full error: ERROR Assertion failed: 100904[donorcentral] (11.0.1.3158) Failed to redo a database operation (id=4, page_no=0xfd4, offset=0x18cd) - Error: Primary key for table 'gl_detail' is not unique : I don't have any newer EBF's for v11 at the moment. Is migrating to v16 something to consider at this point?
(23 May '16, 19:31)
Tom Rolseth
Replies hidden
Sorry, I just noticed 11.0.1.3158 already is the latest v11 EBF on Windows, according to Breck's EBF list... - then trying to do the restore with v12/v16/v17 might be an option (just to test the restore, not necessarily as a real migration!) FWIW, the current EBF readme files are accessible here and they contain several fixes for that assertion - note, I can't tell whether that applies to your situation...
(24 May '16, 03:53)
Volker Barth
|