3

Suppose I do full backup at 12am. Then log backups every hour.

Then suppose at 8.30pm I do a manual log backup (thus truncating the log) and delete this file. Thus the subsequent log backup at 9pm will have incomplete log.

Hence point in time recovery of data from 8pm to 12am is impossible.

Now at 12am there will be another full backup. And subsequently hourly log backups as usual. Now suppose there is crash at 4.30pm.

Does the manual log backup made before this full backup render the normal log backups post the full backup useless? Or are only the logs from last manual log backup upto the last full backup useless?

Improve this question
0

1 Answer 1

Reset to default
6

Then suppose at 8.30pm I do a manual log backup (thus truncating the log) and delete this file. Thus the subsequent log backup at 9pm will have incomplete log.

The transaction log backup created at 8:30 pm contains transaction log records from 8:00 PM through 8:30 PM. The transaction log backup created at 9:00 pm contains transaction log records from 8:30 PM through 9:00 PM.SQL Server does not make a distinction if the backup was created with a SQL Agent job or you manually ran TSQL code.

Hence point in time recovery of data from 8pm to 12am is impossible.

This assumption is right.

Now at 12am there will be another full backup. And subsequently hourly log backups as usual. Now suppose there is crash at 4.30pm. Does the manual log backup made before this full backup render the normal log backups post the full backup useless?

Once you take a full backup at midnight, I rather use it for any crash recovery after that. I would first restore the latest full backup and subsequent transaction log backups. Yes, you can use old full backup but then you have to restore more transaction log backups which is an unnecessary risk.

I suggest you read these documents:

  1. Backups by Microsoft
  2. Misconceptions around the log and log backups: how to convince yourself by Paul Randal
  3. How to tell if a backup log chain is broken?
Improve this answer
3
  • If I use the latest full backup, then do I need to ensure that the log backup that I will apply on top of it covers the time when full backup was taken? So should I use 11pm onwards log backup?
    – variable
    Sep 29, 2021 at 11:22
  • No backup are 100% sure... if your last full backup ended up "corrupted" or lost, you usually have the B plan which is to restore the previous full/diff and ALL the log from this backup to the time you want to restore to. Taking a manual log backup and deleting that file will make this B plan not working. Now... Why do you need to do a manual log backup ? why don't you do that backup with your backup tool (presuming the automatic backups are done with a backup tool)
    – Dominique Boucher
    Sep 29, 2021 at 12:13
  • @variable in regards to your comment about log backups taken during full backup please read What happens to transaction log backups during full backups? by Erik Darling
    – SqlWorldWide
    Sep 29, 2021 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.