Why Backup and Restore?

Backup and Restore is one of those activities which is mundane but high priority and indispensable. Without a proper backup and restore plan there is no disaster recovery and business continuity. Most organizations nowadays have to back up a lot of lot many things. There is database backup, VM backup, application configuration backup, cloud backup, AD backup, system state backup and so on and so forth. Our interest here is to understand how to backup and restore a database.The database holds business information which is a high economic value asset for an organization. More specifically our interest is in understanding and learning the backup strategies and backup and restore process for databases hosted in Microsoft SQL Server. We will go through the different backup types and recovery models available in SQL Server and how they are combined together into a backup strategy to serve the specific needs of an organization.

SQL Server Backup Types

There are quite a few types of backup which Microsoft SQL Server supports.  It is very important to be aware of and understand them and their use cases so that we can make an informed decision with respect to the type of backup or combination of backup types to employ to serve our purpose. We will briefly discuss the different types of backup one by one.

1. Full Backup– A full backup as the name suggests does a point in time backup of the entire database. Entire database means all the objects in the database including tables, views, indexes, triggers, stored procedures etc. Irrespective of the backup plan the first backup taken is always a full backup. A full backup takes the most time to execute.
2. Differential Backup – A differential backup backs up the difference since the last full backup. To be more technically precise it backs up the extents which have changed since the last full backup. SQL server stores data in units of 64 KB extents. Whenever the data in an extent changes after the last full back up the extent is marked for differential backup by setting a flag on so that the extent is backed up on running a differential backup.  Differential backup takes less time than a full backup. Restoration is also easy and less time consuming since only the last full backup and last differential backup suffice for restoration. It is relatively safe and less cumbersome also since you do not have to maintain a large number of backups but only two.
3. File Backup – A file backup facilitates the backup of a select single database file instead of the entire database. This option is applicable if and when you have multiple data files in your database which is not the default case. By default, all the data of a database resides in a single data file. However, if you have multiple large data files then it is a faster and more conducive backup option to back singly as per requirement.
4. FileGroup Backup – A file group backup is similar to file backup but allows you to backup a group of files collectively called a file group. A SQL Server database has a default file group called Primary File Group which is read write. It is possible to create additional file groups and place files in them. The advantage of this is that it allows us to create read only file groups which can be excluded from the backup process and only the read write file groups backed up. This helps to streamline the backup process and eliminate backup of data that does not change by putting them into read only file groups.
5. Transaction log Backup – SQL Server being a stateful application is always undergoing changes. These changes are tracked by and managed by the transaction logs which are responsible for maintaining the database consistency. Transaction log backups are required for point in time restoration. Since the transaction logs contain the details of the ongoing transactions without them a restoration will restore a static database which will miss the transactions that were happening at the time of the backup. The frequency of transaction log backups will depend on the Recovery Point Objective (RPO). Higher the RPO more frequent will the transaction log backups need to be. That is why it is usually automated with scripts which run as per the RPO (say once every 10-15 minutes).
6. Copy-Only Backup – A copy log backup allows us to do an ad-hoc standalone full backup of the database which is not part of the normal backup and recovery schedule and plan. As discussed above the last full backup acts as the differential base for a differential backup. The system keeps track of backups in the backup set using a Log Sequence Number (LSN). The LSN helps to identify the place and position of a backup in the backup chain. If the LSN is disturbed the backup chain is destroyed and the backup plan affected. A copy only backup prevents that from happening and still allows you to take an need based ad-hoc backup.
7. Tail Log Backup– The tail log backup as the name indicates does a full backup of the tail of the transaction log which basically means that it backs up records from the log which have not been backed up by the last transaction log backup. This is required to do a latest point in time restore where you cannot afford to and do not want to lose any data. It is required and done in the following three scenarios.
8. To migrate a live database from one to another server
9. To do a restore of a database which has been damaged and corrupted
10. To do a restore of a database that is not coming online

In all the above-mentioned scenarios a tail-log backup is a must to restore the database and bring it online without any loss of data.

Recovery Models

Recovery model is a database feature or option which determines the backup plan by freezing type of backup operations that can be performed on a database. There are 3 different recovery models in SQL Server. The recovery models are important because they determine the following.

• The type of backup that can be done
• The type of restore that can be done.
• Transaction log management

 Let us examine the different recovery models one by one and what goes with which.

1. Simple Recovery – A simple recovery model does not allow transaction log or tail log backup. It basically means that you cannot do a point in time restore or page restore since you do not have transaction logs to restore the last transactions or damaged pages. You can only do a static restore of the database from the last full, differential or file backup. Also, the transaction log is managed by the database system agent and automatically emptied once it fills up to a certain level to prevent it from filling up the disk. Otherwise transaction logs are cleared post a transaction log backup as there is no need of the old records once it is backed up.
2. Full Recovery – Full Recovery as the name suggests supports a complete point in time recovery of the database and also allows the full set of backup options available. Since it supports all the different backup types including transaction log backup the system does not take care of emptying the transaction log and the transaction log is emptied after a transaction log backup.
3. Bulk-Logged Recovery – Bulk-logged Recovery is almost the same as Full Recovery but with a slight difference. The difference is that it does not log every detail about a bulk operation in the transaction log. This mode of transaction logging is called minimal logging. Bulk operations are those which are outside the normal course of database DDL, DML operations. They are operations performeddeliberately to serve a specific purpose and include bulk manipulation of the data in the database with the help of BULK INSERTS, DELETES etc. Since the transaction log does a minimal logging of these operations it is not possible to do a complete point in time recovery of the database if bulk operations are done on it on a regular basis as the log backup will not have adequate data to restore all the changes made through the bulk operations.

That brings us to the end of the article where we have covered all the basic ideas and terms relevant to backup and restore in Microsoft SQL Server. With this knowledge selecting the right Recovery Model in the Server and performing any of the above-mentioned types of backup is equally easy using either the T-SQL command prompt or SQL Server Management Studio (SSMS). In SSMS it is a cakewalk as all the options are intuitively and neatly presented in a single integrated window.