3

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.

  • Is this an on premise or SQL Server running on a VM in Azure ? How big is the database ? Are you talking about just one database or there are many databases involved ? what version of sql server are you using ? Can you update your question with answers to above questions ! – Kin Shah Oct 1 '15 at 20:53
  • Just updated my OP. Thanks for the heads up. @Kin – PicoDeGallo Oct 1 '15 at 20:59
1

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

  • Awesome information, thank you much. One question regarding the COPY_ONLY backup - what happens if a full backup occurs and this is not set? I ask because there are a few other people with access to the databases that can potentially run a full backup, and while I can advise them to follow the proper steps, I still foresee potential for them forgetting. – PicoDeGallo Oct 2 '15 at 0:58
  • @PicoDeGallo Please see my edited answer. – Kin Shah Oct 2 '15 at 1:53

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.