SQL Server backup and disaster recovery

Data processing becomes more and more important and critical in modern enterprise operations, and may even become a bottleneck of enterprise development. the importance of data protection is self-evident. if an enterprise does not have a good data protection solution or strategy, once important data is lost, the consequences will be disastrous, with economic losses.

 

Major server hardware vendors (such as IBM and HP) provide good data protection policies (hardware or software ). redundant Array of Independent Disks is a good data protection method. for SQL Server, a detailed data backup plan can be developed through the maintenance plan.

 

Data backup policy (full backup, differential backup and transaction log backup)

Data backup is a data recovery service. Before establishing a data backup plan, you should first consider whether the backup can be used to effectively restore data (within the time range allowed by downtime ). the RPO (recovery point objective) and RTO (recovery time objective) permitted by the system should also be considered, the system recovery tool or solution used allows the system or data to be restored to a normal state, the time required ).

 

There are three common backup plans:

(1). Only full backup is supported.

 

When the time period between two full backups fails, data will be lost and can only be recovered to the previous full backup.

 

(2). Full backup + Log backup

By adding log backups between full backups, you can reduce the backup time point to a smaller granularity. you can perform a full backup every day and a log backup every hour or half an hour. in this case, if a fault occurs at, you need to restore a full backup + 23 transaction log backups for a long time.

 

(3). Full backup + differential backup + Log backup

Differential backup (differential backup) is added between full backups, and there is a log backup between differential backups.

 

Which backup policy should be selected based on the actual situation (RTO, RPO, server loading, etc.