11.6 Optimize your backup strategy

11.6 Optimize your backup strategy

11.6.1 the corresponding requirements of the backup strategy

Recovering a database can be considered a solution for database high availability, which is often the last option when a database disaster occurs. As a high availability solution, it must consider the 2 metrics when recovering:

RTO (tolerable offline recovery time)

This indicator is used to measure how long the database can be taken offline. If you are offline for too long, the time cost of recovering a database may exceed the loss of some data.

RPO (tolerable recovery point)

Considering that backups affect the performance of the database, it also requires a cost to store backup media, so you need to weigh the cost factors for database backups. For example, some small businesses require full backups at midnight only, while large enterprises require that data "0 is lost" and therefore use a higher-cost backup strategy.

To design a backup strategy, you may want to consider the following requirements:
(1) Type of backup (full, differential, log backup, etc.) and frequency
(2) The media used for backup (disk, tape, network folder, etc.) and the security of these media
(3) Storage period of backup media (week, month, year, etc.)
(4) Integrity of the backup (whether validation is required during backup, etc.)
(5) Other options (whether compression is enabled to reduce media space, etc.)

Before and after the strategy, please do a good job of testing.

11.6.2 policy When you are in simple recovery mode

The displayed database has 5 backups, and the data that can be recovered to the T5,t6 box in the event of a disaster is lost.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7A/22/wKiom1ai2oHyej8xAABggjrJX5o718.png "title=" Strategy 1-risk assessment. PNG "alt=" Wkiom1ai2ohyej8xaabggjrjx5o718.png "/>

In the simple recovery model, small databases rely on frequent full backups.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7A/21/wKioL1ai2tSj538zAADEo2EVaMA031.png "title=" Strategy 1-Small database. png "alt=" Wkiol1ai2tsj538zaadeo2evama031.png "/>

For large databases, it is not suitable for frequent full backups. As shown, the T1 is a full backup, and the next 3 differential backups are based on T1 as a differential base. At this point, the 3rd difference (that is, T4) is large enough that the next backup (that is, T5) is a full backup and becomes the new differential benchmark.

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/7A/22/wKiom1ai2q-ifwP0AAEBz8MHxe0443.png "title=" Strategy for large databases. png "alt=" Wkiom1ai2q-ifwp0aaebz8mhxe0443.png "/>

11.6.3 Complete policies when recovering a model

Shows the simplest backup strategy for the full recovery model. Db_1 is a full backup, log_1 and log_2 are log backups. If the database has lost data after log_2 (the LDF file is still normal), back up the tail of the active log (tail log) immediately. This relies on db_1, log_1, log_2, and tail logs, which restore all data in turn.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7A/22/wKiom1ai2suC9GJeAABhBB2fWTw559.png "style=" float: none; "title=" Strategy 2-risk assessment. PNG "alt=" Wkiom1ai2suc9gjeaabhbb2fwtw559.png "/>

Shows the addition of a differential backup (T4, T7, T10) inserted between full backups (T1, t13). Data can be recovered to any point in time between T1 to T14.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7A/21/wKioL1ai2wyBGIAKAAEr0OL8YaU010.png "style=" float: none; "title=" policy 2-improved policy. png "alt=" Wkiol1ai2wybgiakaaer0ol8yau010.png "/>

11.6.4 Large-capacity log policies when recovering a model

The bulk-logged recovery model is a special-purpose recovery model that is occasionally used. Its backup strategy is essentially the same as the full recovery model, the main difference being that the bulk-logged recovery model can only be restored to the tail end of the backup, and the full recovery model recovers to any point in time.

This article is from the "SQL Server Administrator's Guide" blog, declined reprint!

11.6 Optimize your backup strategy