Differences between Oracle and SQL Server in data backup and recovery

[This site is exclusively reported by T Expert Network] Before explaining the specific differences between the two, I will first talk about several backup-related concepts. Understanding these concepts is of great significance for understanding the differences between the two databases in data backup.

The first concept is full database backup. Back up all data in the database, regardless of whether the database is updated. In other words, no matter whether the database has been updated recently, the database backup program backs up the data content and updates the data attributes to the backup. In general, in actual work, it is best to perform a full backup of the database no matter whether there are other backup policies after the basic structure of the database is changed or after a large volume of data is updated.

The second concept is differential database backup. Back up the changed data after the last full backup. Differential backup is based on full backup; all files changed after the last full backup are backed up. For example, our backup policy is to make a full backup on Saturday and make a differential backup on other daily backups. Next week, Monday will back up all the changed data in the database after last week's Saturday, while the differential backup next week's Friday will back up all the changed data since the full backup last week's Saturday. It can be seen that the start point of differential backup is always the time after the last full backup.

The third concept is incremental database backup. Incremental Backup backs up all the data that changes after the last backup (including full backup, differential backup, and Incremental Backup. Incremental Backup refers to any backup. For example, our current backup policy is to perform a full backup on Saturday, while for others, perform an incremental backup every day. On Wednesday, the last backup is the data changed after the Tuesday backup. Instead of all data updates that occur after a complete backup on Saturday. Therefore, there is a big difference between differential data backup and incremental data backup.

I. Differences between the two databases in instance and media Backup recovery.

Due to the inconsistent translations of various technical documents, the specific concepts are different. In Oracle databases, three types of Database Backup are called full backup, differential incremental backup, and cumulative incremental backup. Incremental incremental is the Incremental backup we mentioned above, while incremental is the differential backup we mentioned above. In order to unify the text, the author still uses Differential backup and Incremental backup in the following explanation.

First, there is a difference between instance recovery and media recovery in the Oracle database. Instance failure refers to the failure of the database system, such as operating system errors, unexpected power outages, illegal shutdown, background process faults, or the use of shutdown abort to terminate the database instance. Generally, instance recovery is automatically completed by the database system. When an instance fails and the database is restarted again, the database system record the content based on the record of the log file. If the fault may occur, there are also some uncommitted transactions, or committed but not completed transactions, based on which the instance is automatically restored. So far, the SQL Server database has no instance restoration concept.

Media recovery is mainly used to damage database files caused by media faults. When a database file, or some content in a file, or a bad disk partition occurs, or the database is damaged by viruses, data is lost, and so on, in such cases, you must use the media to restore the data. The database administrator must manually mount the latest database backup and log file backup, and execute various recovery commands to restore the media. Media recovery, in SQL Server, is data backup and data recovery. That is to say, the difference between the two databases lies in that Microsoft's SQL Server database does not have the concept of "instance recovery", or has not yet formed a "instance recovery" system, although it technically implements some of the "instance recovery" features.

2. Differences in Backup Types Between the two databases.

Depending on the backup time, database backup can be divided into offline backup and recovery, online backup and recovery, logical backup and recovery.

Offline backup and recovery, some experts turn to cold backup and recovery, which refers to physical backup and recovery of database files when the database is closed, this is the most primitive, simple, and direct method for database backup and recovery. Before offline backup, the database administrator needs to ensure that the database remains offline for a long enough time for full backup and recovery. This is because no one can connect to the database during offline backup. Now both databases support offline backup and recovery.

Online backup and recovery are also called Hot Backup and hot recovery. It refers to the backup and recovery of data during normal use of the database. For Oracle databases, to achieve online backup and recovery, the database must be in archive mode. This is not required for SQL Server databases. For SQL Server databases, if an online backup policy is set, the database automatically adjusts to meet the connection backup and recovery conditions. In addition, Oracle's online backup and recovery functions are complicated to implement, so it must spend more time and energy than Microsoft's databases to test backup and recovery policies. However, it is relatively more flexible.

Logical backup and recovery are unique functions of Oracle databases. Logical backup and recovery refers to the use of tools provided by the Oracle database, such as import and export tools and data pump tools, to back up or restore data in the database. Logical backup and recovery are actually a complete set of solutions that allow data transmission between different operating systems. Assume that there is an Oracle database deployed on the Microsoft operating system, and the content of this database should be moved to the Oracle database on the Linux operating system, you can use this solution to solve the problem. Because the data files exported using logical backup are binary files in Oracle format, which do not include operating system information, data can be directly transmitted between Oracle databases on different operating systems. In addition, this technology can be used to achieve conversion between different Oracle database versions. If we need to upgrade a database of a lower version to a database of a higher version, we can use this technology to leak data from a database of a lower version, then load it into another high-version database. However, some additional work is required to mount a database of a higher version to a database of a lower version. However, many functions of logical backup and recovery are involved in the cross-platform performance of Oracle database systems, such as direct data exchange between databases on different operating systems.