Planning a SQL Server database backup and restore strategy is essential for any production-like database. It helps to minimize loss and maximize availability of data. A complete strategy consists of planning the backup with documentation and then testing the restore options to ensure you are prepared for the worst. The “backup” part includes considering the types and frequency of backups while keeping storage and performance in mind. The “restore” part should take into account the amount of data loss the organization can afford and the amount of downtime that is acceptable to the organization. Four key factors to keep in mind while customizing such a strategy include:
- Amount of data loss (if any) the organization can handle
- Downtime ( of production database ) the company can afford during the restore process
- Amount of disk space available for storage of backup files
- Complexity of the restoration process
Segue has vast expertise and resources within its ranks to help any organization identify the above factors. We help simplify the customization of database backup/recovery solutions and also help maintain them. Upon determining your organization’s answers to the above considerations, you can then begin to plan the backup and recovery components of your process.
Backup Options
As for the different type of backups that are available in MS SQL Server, there are three options:
Full Backups – As the name suggests, this is a complete backup of the database including the logs. It enables point-in-time recovery, but takes time to backup and requires room to store the backup data.
Differential Backups – Differential backups are built on the last Full or Differential Backup. It backs up changes in the database from the last valid backup.
Transaction Log Backups – In Full or Bulk-Logged recovery mode, Transaction Log Backups can be taken at higher frequencies. They help to minimize work loss exposure and also truncate the transaction log.
In choosing the right mix of backups, it is imperative to cater the backups to the needs of the organization. Considerations include:
- Defining the window of time in which the application/database is most actively used, and when there is the least usage. Scheduling Full backups during downtime is preferable.
- Identifying the frequency of writes and updates to the database. If using Simple Recovery Mode, then schedule differential backups between full database backups. In Full Recovery Mode, schedule daily/weekly Full Backups and frequent transactional log backups with less frequent differential backups between them.
- Deciding on the amount of space required to store the backups and clear away or overwrite existing expired backups (Hot tip: Always store these backups on an external server or network drive!).
In a real-world production scenario, it is good to have a mix of Full, Differential and Transaction Log Backups in Full Recovery Mode. The Full Database should be taken daily (preferably) during off-peak hours. Transaction Log Backups can be taken at either 15 minute intervals or half hour or one hour intervals. Differential backups can be taken during mid-day or every hour during the day.
The complexity of the Restore Process is limited if Differential and Transactional Log backups are performed more frequently, because there are fewer files to restore and they can be done faster, therefore requiring less downtime of the organization.
Recovery Models
Microsoft SQL Server Databases can be customized in several ways according to the requirement of the organization. Below are some of the options to consider when planning in the case of Microsoft SQL Server databases. To begin with, SQL Server provides three recovery models. The recovery model is a configurable database property that defines how the transactions are logged and how the database is backed up. They include:
Simple Recovery Mode – As the name suggests, this is the simplest recovery model. It does not allow for transactional log backups and therefore cannot be recovered for a point in time.
Full Recovery Mode – This mode allows for Transactional log backups and therefore can recover a point in time. There is minimal data loss with this mode and it should be preferred in production-type databases.
Bulk-Logged Recovery Mode – This mode is best used when there are bulk-transactions occurring (e.g., nightly/monthly cycle) in databases. Full Recovery Mode can be switched to Bulk-Logged during the bulk-transactions and reverted back during normal data activity.
Customizing a database backup and recovery process to the requirements of the organization is very important. Each organization has unique requirements for amount of data, acceptable data loss, and acceptable restoration time frames. Determining these for your organization will help you to choose the appropriate backup and restore options and to be prepared during any unforeseen situation that causes any system/database failures. Careful planning will help the limit data loss and reduce downtime during such occurrences.