Backup offers an insurance of enterprise data. It can provide you with a timely disaster recovery solution when your computer crashes or when any human operational error occurs. We will discuss two different backup types in this article: logical backup vs. physical backup.
What is the difference between logical and physical backup? Which is better? To come to a clear conclusion, let's first take a closer look at them respectively.
Logical backup creates a copy of database contents such as tables, schemas and procedures which can be exported with EXPORT/IMPORT tool and stored in a binary format. Logical backup is suited for administration and programming. You can restore or move a copy of the database to another platform at a lowest level with all the interdepencies elements.
When your physical backup fails, you can restore the logical backup as an alternative. It’s recommended to perform a full logical backup on a weekly basis.
Physical backup is backing up database directories and files such as data files, control files, log files, executables etc. Physical backup can be divided into hot backup and clod backup and helps to recover the database quickly when error occurs. If you want to have complete recovery data, it is best to perform a full physical backup weekly or monthly while the database is shut down, which aims to keep data consistent. If you only need the recent data, you can perform an incremental backup for the changed data.
Logical vs physical backup, which one to choose? To further compare the them, this section will describe the differences between logical and physical backup of database in the form of a table.
Logical Backup vs Physical Backup
| Comparison | Logical Backup | Physical Backup |
| Recovery Speed | Logical backups are slower and it doesn’t include file system information, so the recovery process is more difficult. | Physical backups can recover the system quickly. |
| Space Taken | Logical backups can back up certain data selectively to save time and storage space. | Physical backup can back up the database completely and takes up more space. |
| Format | Stored in a binary format. | Stored in different formats. |
| Portability | Logical backup can restore the database contents to a different location or environment. | Physical backup is virtually non-portable. |
| Content | The main content of logical backup is the users, tables, stored procedures, etc. of database. | Physical backup creates copies of physical files of the database (data files, control files, archive files, etc.). |
If you have a large database (more than 20 G), the speed of logical backup will be slow and it may affect the performance of the database itself.
It’s better to shut down the database while performing physical backup, and the backup process should be covered as part of your RPO. If you are performing a hot backup, it may result in data loss. In recovery process, you need to pay attention to the directory path, version, configuration, etc. of the recovery data to be consistent with the original data, otherwise there is a risk of data loss.
Most enterprise probably have large databases and need to back up their data regularly to ensure data security. There are various ways to back up a database, the common methods are using the Maintenance Plan or SQL Server Agent in SSMS (SQL Server Management Studio), use the command line or write scripts.
But all these methods have limitations and are quite complicated. So, this section will introduce an automated backup software like AOEMI Centralized Backupper, which can automatically backup database with intuitive GUI.
This software supports Windows 10/8.1/8/7/Vista/XP, Windows Server 2019/2016/2012 (R2)/2008 (R2)/2003 (R2), Microsoft Windows Home Server (WHS) 2011, Windows Small Business Server (SBS) 2011 and allows you to backup SQL Server 2005 - 2019.
Note: You need to assign a static IP to it before installing the Agent. Please download and install Centralized Backupper Database on the central machine, and copy the Centralized Backupper Agent from the “package” folder to client computers for installation. Then click Request Control to those client computers you want to manage. When the client computers Agree to be controlled, you can continue to perform backup and restore.
1. Select SQL Server Backup from the New Task menu.
2. In Step 1 section, click on + Add Computers to select the controlled computers with SQL Server database, then click OK.
3. In Step 2 section, select the client computer from the left side and can click + Add to detect all the SQL Server instances on it.
4. In Step 3 section, click Add Storage to specify a network share or NAS location.
5. Move to the next step, click Schedule to set up an automatic SQL Server backup task. Backup frequency is divided into Once/Daily/Weekly/Monthly, and Full Backup and Differential Backup are also available in Advanced window. After configuring backups, click Start Backup.
As an centralized backup software, it allows you to create a system/disk backup and restore it to another client computer (VM). So, you can also perform P2V or V2V easily.
Comparing logical backup vs physical backup, logical database backups are critical for granular recovery of specific components, and physical backups are useful for full disaster recovery scenarios.
These two backup types are no opposed to each other, and you can combine them depending on your requirements for data and backup frequency.