Introduction
It is a well-known fact that backing up databases is one of the most commonly used tasks for any database administrator regardless of the relational database management system(RDBMS) used. In different RDBMSs, however, the definition, naming, and structure of backup types can be different. Therefore, if a DBA switches from one RDBMS to another, dealing with backup types sometimes can be very confusing. For instance, after working with MS SQL Server databases for a long time, switching to Oracle databases can cause some difficulties. These challenges are mostly related to understanding the differences in database backup types. Thus, in this article, the differences and similarities between MS SQL Server and Oracle databases’ main backup types will be discussed.
MS SQL Server backup types
Here we will discuss SQL Server Full, Differential, and Transaction log backups. There are, however, other types of backups as well, such as copy-only, file, partial backups that are out of this article’s scope. Full database backup contains all database including all changes occurred before the backup finished. The Differential backup includes only those changes that occurred after the last full backup. The latter is called “base of differential”. Any Full backup (except Copy-Only backup) can be a base for a number of differential backups. It is impossible to take a differential backup without having a full backup taken. Transaction Log backup backs up all changes occurred after the previous transaction log backup. In order to take Transaction Log backups, the database must be in the Full or Bulk-Logged recovery model. The simple recovery model does not support transaction log backups. Thus, while each differential backup includes all changes after the last full backup, each transaction log backup includes the changes after the last transaction log backup. To make it easy to understand, let’s illustrate these backup types with examples and pictures. In the picture below, we have taken Full backup at 1 am and 3 differential backups at 2 am, 3 am and 4 am:
In the next picture, we can see that we have taken a full backup at 1 am and transaction log backups every 21 minutes (at 1:21 am, 1:42 am and 2:03 am):
As it is illustrated in the picture, each transaction log backup includes only the changes made after the previous transaction log backup. Therefore, in order to restore the database by the state which it had at 2:03 am, we should restore the Full backup (with NORECOVERY) first, then TrnLog1 backup (with NORECOVERY), after that TrnLog2 (with NORECOVERY), and finally, TrnLog3 (with RECOVERY).
If we have taken a combination of full, differential and transaction log backups like in the picture below, we might have more options to restore the database. In this example, we have set a full backup at 1 am, hourly differential backups and transaction log backups in every 21 minutes:
In case if we need to have the database restored by the state that it had at 2:03 am we have two options. We can restore the Full backup (with NORECOVERY), then Diff backup (with NORECOVERY), and after that TrnLog3 backup (with RECOVERY). Another approach could be using the full and all the three transaction log backups, without using the differential backup. As each transaction log backup contains the changes occurred after the previous log backup and in our example, we have an uninterrupted chain of log backups, we can restore the Full backup (with NORECOVERY), then TrnLog1 and TrnLog2 backups correspondingly (with NORECOVERY) and at the end, TrnLog3 backup (WITH RECOVERY).
Thus, Full, Differential and Transaction Log backups are the most commonly used backup types in MS SQL Server. Having a full backup as a base, and differential and transaction log backup(s) taken after it, it is possible to restore the database by the time the last backup was taken.
Oracle backup types
In Oracle RDBMS, the terminology of backup types a bit differs from MS SQL Server backup terminology. In this article, we will discuss only the Oracle Recovery Manager (RMAN) backup types. RMAN is a powerful tool of Oracle RDBMS aimed at performing backup and recovery. In terms of RMAN terminology, there are Full and Incremental backups. In turn, incremental backups can be either Level 0 or Level 1 backups. In addition, Level 1 backup has two subtypes – Cumulative and Differential. The following picture shows the hierarchy of RMAN backup types:
The Full backup backs up all used data file blocks. This type of backup, however, cannot be served as a base for an incremental backup strategy.
The Level 0 incremental backup includes all used data file blocks like a full backup. Actually, these two backup types are identical with only one exception: unlike a Level 0 backup, a Full backup cannot be served as a base for a Level 1 backup.
The Level 1 Cumulative backup includes all blocks changed after the latest Level 0 backup.
The Level 1 Differential backup backs up all blocks after the latest Level 0 or Level 1 backup. This is the default type of the incremental backup. The picture below illustrates the conception of Level 1 Cumulative backups:
As we can see, the scheme is quite similar to the MS SQL Server differential backups scheme. The next picture shows the Level 1 Differential backups scheme:
This, in turn, looks like MS SQL Server Transaction Log backups scheme. Isn’t it?
Conclusion
All in all, although a Full backup in MS SQL Server can serve as a base for restoring incremental backups, in Oracle RMAN the backup called Full cannot. It is Oracle’s Incremental Level 0 backup which can serve as a base for restoring incremental backups. Anyway, the idea of the Full backup is quite similar in MS SQL Server and Oracle RDBMSs. However, the conceptions and terminology of incremental backups are different. The idea of Differential backups, for instance, is quite different in these RDBMSs. While in MS SQL Server the differential backup includes changes after the last full backup, in Oracle’s RMAN it includes changes after the most recent incremental backup. Therefore, we could say the Level 1 Differential backup in Oracle ideologically similar to the Transaction Log backup in MS SQL Server. Oracle’s Level 1 Cumulative backup is similar to MS SQL Server’s Differential backup. Thus, the confusing thing here is the word “differential” which has different meanings in these two different RDBMSs backup strategies.