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.