Monday, May 6, 2013

Backup is easy, recovery is hard...

"Backup is easy, recovery is hard... especially for someone who believes that backup is easy." - anon admin
Even if you recognize that backup is hard (e.g., you're not really doing backup unless you test recovery), recovery is still ... always ... harder ... than you expect.

Dilbert.com 2000-08-15

Here's a case in point:

Failure to apply transaction log - can I force apply of log?

I have a database that had an assertion failed. When I get the last backup and try to apply the LOG file I get a validation error. Come to find out the indexes on 6 tables needed to be rebuilt. The catch 22 is when I fix the indexes and try to apply the LOG, i get the message that the DB is newer and cannot apply log. Is there any way to force apply the LOG?

That question raises MORE questions, as John Smirnios recognizes in his reply:

I don't think you would get a "validation error" (which I associate with running dbvalid) when applying a log. Exactly what error did you see?

You could try going to an even older backup and applying all logs since that backup.

Also, if you are content with the database with rebuilt indexes, you could translate the log (using dbtran) and apply the SQL against the database using dbisql.

Sometimes, however, it helps to first answer the question that's actually being asked, the one that ends with a question mark: "Is there any way to force apply the LOG?"

Then, once the good or bad news has been delivered, the listener may be more receptive as the discussion wanders further afield:

In addition to what John said...

As you know, or have discovered, you can't apply log files out of order in recovery mode, which is what happens when you start a backup database normally and run new transactions (e.g., reorganize indexes), and then try to apply an old log file in recovery mode that has older transactions.

If possible, try starting over... and document EXACTLY what steps you take and EXACTLY what results and/or error messages you get so we can help.

If there are any differences between what you did (or are doing) and the following steps, please describe those differences as well.
  1. Get (copy) the backup xxx.db file and the associated backup xxx.log file. Make copies so you can go back and start over if necessary.

  2. Apply the copy of the backup xxx.log file in recovery mode to the copy of the backup xxx.db file via dbsrv12.exe xxx.db -a xxx.log

  3. Get (copy) the recent xxx.log file that has the transactions up to when the assertion error. This assumes there are no OTHER log files between the one applied in step 2 and this one.

  4. Apply the copy of the recent xxx.log file in recovery mode to the xxx.db file via dbsrv12.exe xxx.db -a xxx.log

  5. Start the database normally (no -a xxx.log option).
You may find some ideas in the following blog posts:


Apparently April is "Backup And Recovery Month" in my world, at least it was back then :)


2 comments:

john bell said...

I have used this tool but I am not fully satisfied from this tool. When I have used the sql database recovery tool then I satisfied. So many options available in the tool that is not available in the tool that is given here.

Breck Carter said...

Dear john bell,

> I have used this tool but I am not fully satisfied from this tool.

Terrible news, that is! My fervent hope that you become fully satisfied from your tool, it is.

> When I have used the sql database recovery tool then I satisfied.

Wonderful news that is! But are you "fully satisfied" or merely "satisfied" from your tool?

> So many options available in the tool that is not available in the tool that is given here.

Oh, yes, many more options, especially the one about it working with SQL Server as opposed to SQL Anywhere.

Sure your tool will satisfy you long time, am I. Yeesssssss.