MS SQL Maintenance Plans – Backup Type and Rotation Frequency.
In an earlier blog article, I discussed using the MS SQL Management Studio Maintenance Plan Wizard to help you set up core maintenance tasks, which should also include a database backup routine, that is coupled with a backup rotation clean-up task. How this is configured is largely based upon the following factors:
1. What type of database recovery method is being used.
MS SQL allows use of (3) three recovery methods as:
a. Simple
-
Does no backup transaction logs..
-
Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.
-
Work Loss Risk: Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.
-
Recover to a point in time?: Can recover only to the end of a backup.
Notes: This method assumes that you will be restoring the complete, whole database backup which would overwrite the existing current database. So with a typical nightly based backup strategy, restoring from last nights backup would completely overwrite the current database. If something occurred later in the day that required a restoration, you would loose the current days data.
This method also allows you to set-up a "Shrink Database" routine, which also "truncates" the database transaction log, thereby reducing it’s size, and the overall disk space requirement.
*The advantage here is that the restoration process is easier.
b. Full
-
Requires log backups.
-
Work Loss Risk: Normally, none. No work is lost due to a lost or damaged data file.
-
Recover to a Point in Time?: Can recover to an arbitrary point in time (for example, prior to an application or user error).
-
If the tail of the log is damaged, changes since the most recent log backup must be redone.
-
Can recover to a specific point in time, assuming that your backups are complete up to that point in time.
Notes: Unlike, the "Simple" method, the database transaction log, cannot be shrunk/truncated automatically because doing so would remove data required for part of a restoration process.
*This would be the preferred method if disk space were not an issue, backup were being made frequently, throughout the day, and you need to restore a real-time backup (say about 30 minutes ago).
c. Bulk logged
-
Requires log backups.
-
Works as an adjunct of the full recovery model that permits high-performance bulk copy operations.
-
Reduces log space usage by bulk logging most bulk operations. If the log is damaged or bulk-logged operations occurred since the most recent log backup, changes since that last backup must be redone. Otherwise, no work is lost.
-
Recover to a point in time?: Can recover to the end of any backup. Point-in-time recovery is not supported.
***For most applications the "Simple" or "Full" are appropriate. ***
2. The frequency of the database backup and what other maintenance tasks are linked to it.
3. The amount of hard disk space available.
Whenever practical, making a nightly backup, with a least a (7) seven day rotational cycle should be done.
However, if the database and log file were a combined total of 30 GB, then a (7) seven day rotational backup at that current size would require over 210 GB of free space.
Also, most external back-up media programs will not make a back-up of a open SQL Database. Doing this may require a scheduled task that stops the MS SQL Services, and then restarts them after the backup process has completed.
4. Whether Multi-Site or other data Replication is being used.
Even though MS SQL allows for a backup to run on an actively used database, because of the amount and type of data moving back and forth during replication tasks, back-ups are not recommend during these operations. Like in #2 above, this is also a greater issue if you have other maintenance tasks linked to the backup that require exclusive access and control over the database. The "Simple" recovery method is preferred for use with CP SQL and Peer Direct when Multi-Site is involved.
Please contact the CCS Retail Systems Support Department if you have questions on the above, or to schedule your maintenance plan review your existing set-up.