Backup strategies

Full Backup—Simple Mode

When you follow this strategy, the DBA configures the database recovery model to simple, and performs periodic Full Database backups. This is the simplest backup strategy, and the simplest to restore. But it is also the one that takes the longest to back up and is not feasible in most scenarios. You can use this strategy in the following cases:

• Development Databases: Databases used in developing or testing environments, if you can afford to lose daily work, otherwise consider a Full and Log Backup strategy.

• Stage Databases: Databases used in Data Warehouse to stored intermediate data extracted from source systems.

• Read Only Databases: Databases that have only read only information, for example subscriber databases that store information for reporting purposes.

• System Databases: master, msdb and distribution.

Log & Full Backup

This strategy is similar to the previous one; however, instead of changing the recovery model of the database, you back up the log just before the full backup. This approach has the advantage of capturing not only the data, but also the changes that occurred since the last backup. The benefits of a Full & Log backup strategy are:

1) If Database files and Log are physically separated and the database hard drive fails, you may back up the Log and not lose any data.

2) In case of a software failure, human error (end user or DBA) or security breach; the DBA can first backup the transaction log first and then restore the database to a point in time, reducing the amount of lost data. Leaving the full recovery model on, allows you to backup the transaction log first and restore the database and the part of the transactions up to the last known good reliable state.

3) If the database has multiple files and one fails, you can first backup the tail of the transaction log (that include the transactions that occurred since the last log backup), and using the last full backup restore only the failed file and the transactional log to restore transactional integrity.

4) The transactional log backups can be used in forensic analysis.

Full & Log Backup

In this scenario, the DBA schedules one full backup and multiple Log backups, between full backups. This plan has the advantage of reducing the amount of lost information in case of total server collapse, because you can schedule log backups more regularly than you can schedule full backups.

Consider the use of the Full & Log backup strategy in 8x5 databases, and non-volatile 24x7 databases. For example, you may schedule nightly Full Database backups and Log backups every four hours.

Full – Differential – Log

Sometimes the information stored in a database is so valuable that you want to back up the information very often. However, relying on backup logs is not feasible because, in case of failure, you will have to restore every log backup since the last full backup. In this case, the Full – Differential and Log strategy may help.

When using Full, Differential, and Log Backups the DBA schedules a Full Backup occasionally, differential backups often, and Logs very often. In this case, occasionally means as frequently as needed, to minimize the impact of a full backup, but enough to avoid differential backups to grow too much; often means as frequently as needed, to minimize the impact of restoring too many log backups; and very often means as frequently as needed to minimize data loss exposure. Some implementations of this strategy may be:

1) Full backup every month, differential backups every night, and log backups every two hours.

2) Full backup every week, differential backups every 6 hours, and logs every fifteen minutes.