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?

TIA,

Kevin

asked 03 May '13, 17:11

kakkarnold's gravatar image

kakkarnold
31113
accept rate: 0%

edited 04 May '13, 11:35

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270


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.

permanent link

answered 03 May '13, 22:03

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

edited 03 May '13, 22:10

Thanks John. I was able to translate the log and then apply using ISQL. I had a couple of other issues to work thorugh but the db is back up to date and working like a champ. Thanks again for your help.

(07 May '13, 14:28) kakkarnold

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:

April 1, 2011 Demonstrating Full and Incremental Backups

April 6, 2011 Quiz: Which log backups are required to restore a database?

April 8, 2011 Recovering Across Multiple Backup Generations

April 18, 2011 Answer: Which log backups are required to restore a database?

April 22, 2011 Validating Backups

April 25, 2011 Demonstrating Backup, Validation and Restore

permanent link

answered 05 May '13, 09:29

Breck%20Carter's gravatar image

Breck Carter
25.8k428592853
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×47
×33
×31
×27

question asked: 03 May '13, 17:11

question was seen: 1,305 times

last updated: 07 May '13, 14:28