Sql-server – Weekly full backups and daily differential backups, saving the full backups from each week

backupmaintenancesql server

I am wanting to create a maintenance plan that performs a full backup that is done every Sunday morning. After work hours on Monday through Friday, I want to perform a differential backup based on this full backup. However, instead of overwriting the previous week's full backup, I want to retain that full backup for historic records/off-siting while getting rid of the differentials associated with that full backup. The type of plan I am planning on creating is as such:

  • If needed, a rebuild of the database indexes is done beginning early AM Sunday morning.
    • A further caveat to this would be to reorganize + update statistics instead based on the amount of fragmentation present.
  • Following the index maintenance, a full backup is created that same morning.
  • Monday through Friday at night, a differential backup is taken based on the full backup.
  • Following the differential, a DB integrity check is ran.
  • The next Sunday arrives, a full backup is established, the previous week’s differentials removed, and the previous full backup is brought here locally to a Drobo after-hours.

The reasoning for this type of plan is due to HDD size restrictions on the Azure environment I am working with. Given that, is this a sound strategy, or where should I seek improvement? Second, how do I go about retaining the previous week's full database backups but NOT the differentials? The articles I've read thus far seem to indicate that a revolving backup of this nature will have both the full backups and differentials removed.

This is a SQL Server 2012 instance on a Windows Server 2012 VM on the Azure environment. There are 5 databases, ranging from 6GB to 10GB each, that will be affected by this type of plan.

Thanks for your assistance.

Best Answer

As you have stated, your backup strategy can be viewed as below :

enter image description here

The reasoning for this type of plan is due to HDD size restrictions on the Azure environment I am working with.

Two imporatant things that you can do

  1. Enable Instant file initialization (do follow other best practices when runing SQL Server on Azure VM)
  2. Enable backup compression and take backups with WITH COMPRESSION

Given that, is this a sound strategy, or where should I seek improvement?

From the above figure, there is a clear room for improvement. You should take a differential backup on Sat (just incase if a disaster happens). Also, its important to read Backups Gone Bad: The Danger of Differentials. Make sure if you have to take a full backup in the middle of your backup strategy, you use COPY_ONLY backup.

Second, how do I go about retaining the previous week's full database backups but NOT the differentials?

Dont use maintenance plans as they have noticeable drawbacks. I highly recommend to use Ola's backup solution (and Index maintenance solution as well).

Edit : As per your comment:

If a full backup occurs in the middle, your differentials would be useless, since the base has changed and you dont know who took the last full backup. The differential relies on the last full backup. If you know people will take adhoc full backups that will mess up your differentials then deny them backup rights and create a stored procedure that will take full backup with COPY_ONLY option and grant those people execute rights to that SP. Additionally, do concentrate on recovery model of the databases - Full recovery model has to have a log backup.

You could even query msdb.dbo.backupset where is_copy_only = 0 to find out user_name of users who are taking COPY_ONLY backups

Just adding this for completeness - Restricting users to COPY ONLY LOG backups

Related Question