SQL Server 2008 – Two FULL backups in one day

Posted on

Question :

There is a database backed up by NetBackup once a day (Full backup 6PM to tape + 3/4x log backup per day). Database is in FULL recovery mode.
Vendor of application has remote access and he scheduled his own FULL backup to the file system (at midnight, probably directly in application).

Would be there a problem with POINT IN TIME recovery? Does his backup broke our chain?

In this picture, yellow are vendor’s FULL backups to filesystem. Thank you guys

enter image description here

Answer :

Would be there a problem with POINT IN TIME recovery? Does his backup broke our chain?

No, Full Backup has no affect on transaction log backup chain. You would still be able to restore full backup and all log backups in sequence. So let us say you have following

  1. Full backup 1 taken by you
  2. Log backup 1
  3. log backup 2
  4. Vendor Full backup 1
  5. Log backup 3
  6. Log backup 4
  7. Vendor Full backup 2
  8. Log backup 5
    In case of disaster you would be able to restore Full backup 1 and then log backups 1,2,3,4,5 in sequence.

Having said that you can ask vendor to take copy_only full backups just to be on safer side and simplify things

Hope this is what you are asking.

In addition to Shanky’s reply (I do not disagree in any way with that):

One might argue that the vendor should use COPY_ONLY for its backup. Not that it affects restoreability in any way. But as sort of an indicator. I can sympathize with that.

One can also argue that the vendor should use COPY_ONLY so that the restore dialog in SSMS won’t use that backup as a restore point. The argument is that this backup might not exist as accessible for the db engine (it might be a VIRTUAL_DEVICE, for instance, or an ad-hoc backup where the backup file was deleted after usage). But, SSMS might still be trying to use this COPY_ONLY as restore point in some cases. And when it does, you won’t be able to do the restore based on the backup history. I just like to add this bit, so that one don’t equate COPY_ONLY with “SSMS won’t base a restore from it”. FWIW.

Leave a Reply

Your email address will not be published. Required fields are marked *