There are several types of backups to consider when planning a backup strategy. In this post I am going to talk about different backup types and talk through the strategies.
The following list summarizes the database backup types:
- Full Database Backups – A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished.
- Differential Backups – A differential backup is based on the most recent, previous full backup of the data that is included in the differential backup. A differential backup captures only the data that has changed since that full backup. This is known as the base of the differential. A differential backup includes only the data that have changed since the differential base.
- Transaction Log Backups – Under the full and bulk-logged recovery models, taking routine backups of transaction logs (log backups) is necessary for recovering data. With log backups, you can recover the database to the point of failure or to a specific point in time. Best practice is to perform backups frequently enough log backups to support your business requirements, specifically your tolerance for work loss such as might be caused by a damaged log drive. The appropriate frequency for taking log backups depends on your tolerance for work-loss exposure balanced by how many log backups you can store, manage, and, potentially, restore.
- Snapshot Backups – SQL Server supports snapshot backup and restore technologies (SQL Server snapshot backup) together with independent hardware and software vendors. Snapshot backups minimize or eliminate the use of SQL Server resources to accomplish the backup. This is especially useful for moderate to very large databases in which availability is very important.
Designing the Backup Strategy
After you have selected a recovery model that meets your business requirements for a specific database, you have to plan and implement a corresponding backup strategy.
Backups should be designed on an individual database level to meet the requirements established by the business for that individual database. The following section describes the backup strategies that can be enacted for various scenarios and individual activities involved in those activities.
Simple Recovery Mode Databases
For databases with little transactional activity, low risk if data is lost, or so designated by a third party software vendor, a simple recovery mode database is relatively simple to backup. The database will only support full or differential backups. It is simplest to use full backups unless the size of the database is prohibitive. In that case, periodic full backups can be scheduled, and differentials can be done more frequently to minimize the size of the backup. Only the last full and differential backup (is there is one) is required to restore the database.
The risk for data loss can be determined by the length between backups. If the database is backed up once per day, then 24 hours of data are at risk for that period (potentially more if database backup failures are not monitored and corrected promptly).
Full Recovery Mode Databases
If the database is set to full recovery mode, there are different backup scenarios that can be used. The baseline is a full database backup. This will be the largest and lengthiest backup, but provides a comprehensive copy of the database at a specific point of time.
Once a full backup is made, a differential backup can be made. Differential backups are a copy of the changes to the database since the last full backup. The full backup is restored, and then the latest differential is applied to bring the database to the state in which the database was at the point of the backup. The differential copies are smaller and can be completed more quickly than a full backup. Only final, committed changes to the database are backed up, not individual changes.
Transaction log backups provide a record of each individual change made to the database. The transaction log can be applied to replay the changes made to the database. This allows point-in time recovery. Transaction log backups tend to be small and fast.
Transaction log backups can be applied to a database restored from a full backup or a full and a differential backup. Multiple transaction log backups can be restored until the proper point in time is reached. Thus if a transaction log backup is made every hour, the logs would be restored in order until all the logs have been applied to bring the database back to the desired point of time.
The following is the recommended way to backup databases 1.) Perform a full database backup once each week, 2.) Then perform a differential backup once each evening except when the full backup is run 3.) And lastly, a transaction log backup every hour.
Here is a table to get you started. You will need to adjust based on your specific business needs.
Database Backup TypeFrequencyDescriptionFullWeekly
Put the database in Full recovery mode and automate a full database backup via a TSQL scriptUse SQL Server database backup compression
Backup the database in parallel by using multiple files matching the number of CPUs
DifferentialDailyMonday – Saturday
12 AM
Same guidelines as aboveTransactionalHourly1:00 AM
Same guidelines as above
Recommendation:
- Backup LUN – Place backups on separate physical spindles. Do not backup to the same location as your database data files. The backup LUN should be able to hold a week’s worth of backups to speed up the recovery process
- As backup disk I/O is sequential and write, for optimal performance and redundancy place backups on a RAID-1 volume
- Regularly (at least quarterly) test the restore of backups to ensure the backups are successfully working. This also ensures that the operation support team is aware of the steps to restore a database which can lead to a speedy and less stressful recovery during a disaster
- Plan to use the backup compression feature of SQL Server Enterprise Edition. By setting the compression option in your backup script, or by configuring the application server running SQL Server Enterprise Edition to compress by default, you can significantly decrease the size of your database backups and shipped logs.