Backup and Recovery Models in SQL Server Database

Overview

Backup is the most essential for the protection of data on which whole business is dependent. In a way to protect the data, user should maintain periodic backup of data, which is to be monitored and verified. The backup part strategies define the frequency and type of data backups, speed, and nature of hardware, which is required.

SQL Server is a service that manages its own data files due to which users cannot just copy .mdf file to maintain the data backup. It is significant responsibility as there are various tools available that helps to develop DRP (disaster recovery plan) for SQL data. It is important to plan by considering the downtime tolerance and other factors. The simplest way to implement DRP is by utilizing the in-built SQL server tools. The server should be set up for periodically backup the databases to perform data backup timely. All the Recovery Models should be set properly whether the user is utilizing online software to perform live backups of SQL Server. There are two types of Recovery Models available on SQL Server; users must manage the type of model accordingly as mentioned in the following section.

Full Recovery Model

It allows user to recover database at certain point of time, not as the timely backups are made. Under this model, the database performs differently under this model. It saves all the data in Transaction Log, after it is committed to database until transaction log is backed up. Its size remains the same but it is emptied and new transactions are filled into it until new transactions are piled. It is recommended that organizations should perform transaction log backups every hour daily because the log empties when it is backed up. If the translations are taking place while logs are backing up then, it will not clear completely and will have white space between transactions. It results in fragmentation and various other issues. Therefore, backups should be performed before and after hours as it provides chance to clear out. The most difficult task is to manage space under this model.

Note: If user is having setup of Full Recovery Model then, it is not necessary to perform transaction log backups, it will never be empty, it will keep filling.

Simple Recovery Model

It allows restoring at time of last backup (backup of database not Log). It is quite different from Full Recovery Model. In it, the transaction passes through log but never stays over it. They are directly committed to the database. The log is never grown under this model. This is usually for organization of low transaction, which can live with loss of work since it restores to last complete backup.

Periodic Backups

Another part of SQL Recovery Plan would be to execute periodic backups in server. These would be set up to fit the Recovery Model, which user is utilizing. There are three types of backups as mentioned below:

• Full Backups
  It takes the complete backup of SQL Server database to maintain all data records. Users can restore the complete database from full back up.
• Differential Backups
  It helps to back up the differential database of SQL Server. It takes backup of only changes since last full or differential backup. Users need the last complete backup as well as all the differentials until now for restoring the database.
• It takes the log data into backup. It can be restored completely then, restore all the transaction logs until the required time.

Note: Old backups should be firstly backed up off the server and then purged when it is more than 10 days or week older. SQL Server has an inbuilt tool to remove it.

Instead of this, users can also run whole procedure via backup command within SQL Server. This command helps to create whole page-by-page backup, creates replica of database, which includes each object within database and complete data.

Conclusion

Backups play a major role to restore the data in case of disaster. There is a great importance of backups in SQL Server to have a recovery plan. It is a responsibility to make monitor recovery plan. It should be considered which Recovery Model in server would fit according to recovery plan and perform backups in SQL Server by using in-built tools. It is recommended to backup, monitor, as well as tests the databases. It is recommended to have document of backups, restores process, and maintain a copy of documentation on running book. However, if the above strategy fails in case of disaster or the appropriate backup strategy were not applied, one can use SQL database recovery software to perform the recovery operation.