recent transaction log backup was crashed.

Question

We have a Full backup at 1.00am, and differential backup at  ever 4 hours, and transaction log backup is at every 2min.Today after some time 17th transactional backup was crashed.How to restore if 17th backup was corrupted?

Please suggest me on the same

Answer 1

Hi,

In any case if the log backup chain is broken you cannot restore beyond that point.

In your case you can recover data up to 16th Log backup only.

Also, If the backup has failed it may not mean that transaction logs are corrupted. If only the operation has failed you can try and take the backup again and try restoring that after applying 16th log backup.

---

- Vishal

SqlAndMe.com

Answer 2

hi there,

can you please explain this "17th transactional backup was crashed".

If your database crashed after 17 the transaction log backup or 16th transaction log backup.

follow the below steps:

1) take the tail of the transaction log backup first from the crashed database.

2) restore your last night backup with no recovery.

3) restore recent differencial backup with no recovery

4) restore every transaction log backup after your recent differencial back up in order with no recovery

5) restore your tail of the transaction log backup with recovery

do some consistency checks and have agood feeling that your database is ok.

then setup your logshipping again.

you need to consider few things:

as your transaction log backup frequency is every 2 mins you need to increase the frequency of differencial backups. i recommend every 1 hr.you may need more space but create a job to delete the older files.

good luck

kumar

Answer 3

Hi

Its kind a interview question for L1.

If your database is crashed :--->Answer is take Tail log backup if log file is acessible then Restore(Latest Full backup + Latest differential + All t-log backup from last differential+ tail log backup with stop at just before database creashed). 

If log backup is crashed :---> Start log backup again if it fails again take full backup and whole backup taking story will start again.

Thanks

Saurabh Sinha

Please click the Mark as answerbutton and voteas helpful if this reply solves your problem

Answer 4

what ever May be its like-

latest full backup restore with no recovery followed by latest differential backup after the last latest full backup (with no recovery)+any additional T-log backups(after full+Diff restore done) till last log backup completed, also you can take the Tail T-log backup in order to achieve latest point -in time but you said your log backup corrupted -no its not t have been failed with proper reason(that you can see from the errorlogs/event logs or from the job output/history).

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.