SQL Server Maintenance Plan

Question

Dear Guys,

I need you advice and recommendation regarding the SQL Server Maintenance plane as I have 2 maintenance planes, one is locally and the other one is to the Cloud. The local one is configured as a daily FULL backup to our local storages and the Cloud one is configured with 3 sub-planes as one FULL backup every 10 days and one Differential backup once a day and Truncation log backup every 30min.

So, I'm wondering, Is the Differential backup and the Truncation log backup is related and covered by its maintenance plan (Cloud Backup) or they're covered from the last FULL backup taken from my Local Backup "the other maintenance plan"

As per my knowledge, the Differential backup and the Truncation log backup covered and related to the last FULL backup taken to the database.

Thanks for your understanding and waiting your advise!

Answer 1

DIFF and TLog backups are always related to the latest FULL backup and they don't care which job has taken the latest FULL backup.

So according to your explanations, your daily DIFFs and TLogs (Cloud MP) are based on your latest daily FULL backup (local MP). => mixture of job results
and I don't think that's something you wanted to achieve...

My recommendation would be - with a target of having the possibility to restore point-in-time - to use the Cloud MP for your regular backups and change the local one to a "Copy Only" FULL backup.

Answer 2

@Bjoern Peters Just to make it clear for my exiting scenario, I can normally restore my "FULL backup copy only" from the Local MP without any issue nothing is missing in the DB. on the other hand I can restore the FULL backup then the DIFFs backup then TLogs backup from the Cloud storage.

• Cloud backup is at the following configuration:
  3 sub-planes as one FULL backup every 10 days at 07:00 AM and one Differential backup once a day at 07:15 AM and Truncation log backup every 30min.

Is it right and that's the best practice ?

Thank you in advance

Answer 3

DIFF and TLog backups are always related to the latest FULL backup and they don't care which job has taken the latest FULL backup.

I will need to disagree with this. You make it sound like they are equivalent. They are not.

A DIFF backup is tied to a certain full backup, and without that full backup, that diff backup is useless.

A log backup, on the other hand, relates to the preceding log backup, which relates to the one before that. To restore log backups, you must start with restoring a full backup plus any diff backup. However, as long as you have an unbroken chain of log backup, you can restore a full backup from three weeks ago and then apply log backups. And which you may have to do, if you have corruption that has been unnoticed for some time.

As for COPY_ONLY, this is full backup that does not render the preceding diff backup useless.