transaction log backup failed

wasabi3689
wasabi3689 used Ask the Experts™
on
I have a full backup every weekend, and every 30 min transaction log backup. Today I found out one transaction log backup fail last night but after 30 min, the transaction log backup runs again, it succeeded, then continue to run succeeded.

I want to know if I should go ahead to run a full backup to ignore the failed transaction log backup, no need to wait to weekend. My reason is I cannot restore all the logs if something happened now. I have to use last week's full backup + all the transaction logs before next weekend full backup. Since one of the transaction log failed, I am not able to restore to point in time, Am I right?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> Today I found out one transaction log backup fail last night but after 30 min, the transaction log backup runs again, it succeeded

Transaction backup that succeeded would have covered the failed duration as well and hence your backup chain is not broken and covered well.

>> My reason is I cannot restore all the logs if something happened now. I have to use last week's full backup + all the transaction logs before next weekend full backup.

I would recommend changing few things for faster restoration. lets say if something happens to your database on Friday night, then you ideally need to restore Weekend backup followed by all Log backups which would be 100+ files and makes your tasks complex..
Instead I would suggest you to do the below:
1. Weekend - Full Backup
2. Every Day - Differential Backup
3. 30 Mins - log backup

So, for the previous scenario, you would need to restore Weekend Full backup and latest Differential Backup(one taken on Friday) and then all Log backups.. This will help save you a lot of time..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> Since one of the transaction log failed, I am not able to restore to point in time, Am I right?

No, if your consecutive transaction log backup was successful, then your log backup chains are intact and you can do point in time recovery.
Apurva BhandariLinux/Cloud/DB/DevOps

Commented:
Every 30min backup is not a good idea as its slow down the transaction speed. if you need 100% transaction rate then make plan for the master-slave replication configuration or multi-source mssql config so that at the end of the day or week you will get all the data.

About backup, you can take daily backup checking the transaction flow and at the weekend incremental backup type of architecture.So suppose one of the DB got fail you can recover all the data. or more to this you can make app level changes in configuration and the transaction will hit to the 2 different DB at the same time so that none of the txn will be missed.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Every 30min backup is not a good idea as its slow down the transaction speed

Hi Apurva, I'm afraid that your suggestion was invalid as it is not applicable for SQL Server databases..
Hence haven't recommended your comment as solution in the recommendation.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial