Comparing SQL Server and Oracle Backup Types

By: Sergey Gigoyan | Updated: 2024-07-24 | Comments | Related: > SQL Server and Oracle Comparison

Problem

It is a well-known fact that backing up databases is one of the most common tasks for any database administrator (DBA), regardless of the relational database management system (RDBMS) used. However, the definition, naming, and structure of backup types can differ for each RDBMS. Therefore, if a DBA switches from one RDBMS to another, dealing with backup types can sometimes be very confusing. For instance, switching to Oracle databases after working with SQL Server databases for a long time can cause some difficulties. DBAs can work through these challenges by understanding the differences in database backup types.

Solution

In this article, we will discuss the differences and similarities between the main backup types of SQL Server and Oracle databases.

SQL Server Backup Types

Let's begin by discussing SQL Server backups: Full, Differential, and Transaction Log.

There are, however, other types of backups as well, such as Copy-Only, File, and Partial backups that are beyond this article's scope.

The Full database backup contains the entire database, including all changes that occurred before the backup finished.

The Differential backup includes only those changes that occurred after the last Full backup. The latter is called the "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.

The Transaction Log backup backs up all changes that 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 easier to understand, let's illustrate these backup types with examples and images. In the image below, a Full backup has been taken at 1:00 AM and three Differential backups at 2:00 AM, 3:00 AM, and 4:00 AM:

The next image shows that we have taken a Full backup at 1:00 AM and Transaction Log backups every 21 minutes (at 1:21 AM, 1:42 AM, and 2:03 AM):

Full backup and 3 Transaction log backups

Each Transaction Log backup includes only the changes made after the previous Transaction Log backup. Therefore, to restore the database to its state at 2:03 AM, we should first restore the Full backup (with NORECOVERY), then the TrnLog1 backup (with NORECOVERY), then TrnLog2 (with NORECOVERY), and finally, TrnLog3 (with RECOVERY).

If we take a combination of Full, Differential, and Transaction Log backups like in the image below, we might have more options to restore the database. In this example, we have scheduled a Full backup at 1:00 AM, hourly Differential backups, and Transaction Log backups every 21 minutes:

Full backup, Differential backups, and Transaction Log backups

If we need to restore the database to its state at 2:03 AM, we have two options:

Option 1: Restore the Full backup (with NORECOVERY), then the Differential backup (with NORECOVERY), and finally the TrnLog3 backup (with RECOVERY).
Option 2: Use the Full backup and all three Transaction Log backups without using the Differential backup. As each Transaction Log backup contains the changes that 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 SQL Server. With a Full backup as a base and Differential and Transaction Log backup(s) taken after it, it is possible to restore the database to the time the last backup was taken.

Oracle Backup Types

In Oracle RDBMS, the terminology of backup types is a bit different from SQL Server backup terminology. We will discuss only the Oracle Recovery Manager (RMAN) backup types. RMAN is a powerful Oracle RDBMS tool 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, a 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, unfortunately, cannot serve as a base for an Incremental backup strategy.

The Level 0 Incremental backup includes all used data file blocks like a Full backup. Interestingly, these two backup types are identical with only one exception: unlike a Level 0 backup, a Full backup cannot serve 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 image below illustrates the concept of Level 1 Cumulative backups:

As we can see, the scheme is quite similar to the SQL Server Differential backups scheme.

The next image shows the Level 1 Differential backups scheme:

This, in turn, looks like the SQL Server Transaction Log backup scheme, doesn't it?

Conclusion

Overall, although a Full backup in SQL Server can serve as a base for restoring incremental backups, the Oracle RMAN backup called Full cannot.

Oracle's Incremental Level 0 backup can serve as a base for restoring incremental backups.

The idea of a Full backup is quite similar in both 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. In SQL Server, the Differential backup includes changes after the last Full backup, but in Oracle's RMAN, it includes changes after the most recent incremental backup. Therefore, we could say the Level 1 Differential backup in Oracle is ideologically similar to the Transaction Log backup in SQL Server.

Oracle's Level 1 Cumulative backup is similar to SQL Server's Differential backup. Thus, the confusing thing here is the word "differential," which has different meanings in these two different RDBMS backup strategies.

Next Steps

For additional information, please follow the links below:

About the author

Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-07-24