0

I am new to database admin role and I have to configure backups for a new client.

I want an advice on how to handle below scenario. I want to configure a weekly full backup along with incremental transaction log backups running frequently. Lets say I do full backup every Sunday and incremental transaction log backup every 30 minutes and I get

TL1--> TL2--> TL3-->TL4--> {diskspace full auto backup could not complete successfully have to run backup DB command to finish the backup manually after freeing diskspace} -->TL6-->TL..

My problem and question here are that

  1. Is there any way I can execute the backup command without breaking my auto backup chain of incremental logs. I am thinking to update the column backup_finish_date of msdb.dbo.backupset with the time I execute the backup command, is it a viable option?

  2. Is supposed in my scenario a break did occur in my log backup chain , how can I restore normal flow

Improve this question
2
  • if a backup failed, you don't have to do a new backup manually? just wait for the next automatic run. Full backups won't break the chain either
    – siggemannen
    Nov 15 at 12:06
  • Having said that, you want to break the chain, because otherwise you would need to restore millions of log records to be able to get to a point in time. It's only useful to use COPY_ONLY if you are doing an extra full backup. If you only have a single full backup job then you can restore that, and work forwards from the log backups.
    – Charlieface
    Nov 15 at 15:10

2 Answers 2

Reset to default
3

If you use the COPY_ONLY option, the new backup does not interrupt (or otherwise take part in) your normal backup chain.

Improve this answer
0

Is supposed in my scenario a break did occur in my log backup chain , how can I restore normal flow

Differential backup let you jump over gaps in log backup chain.

Improve this answer