SQL Server备份和还原过程概述

最新推荐文章于 2024-07-29 18:16:59 发布

culuo4781

最新推荐文章于 2024-07-29 18:16:59 发布

阅读量371

点赞数

文章标签：数据库 java 大数据面试 python

原文链接：https://www.sqlshack.com/overview-sql-server-backup-restore-process/

版权

In a manner of speaking, planning and implementing a SQL Server backup design is an art. Backup, Restoration, Recovery, Business Continuity Plans (BCP), and Disaster Recovery (DR) are different phases of data revolving around the discussions involving data backup. In other words, it’s about how we ensure to retain the business data through any sort of situation that’s thrown at us.

从某种意义上说，计划和实施SQL Server备份设计是一门艺术。备份，还原，恢复，业务连续性计划（BCP）和灾难恢复（DR）是数据的不同阶段，围绕涉及数据备份的讨论。换句话说，这是关于我们如何确保在遇到任何情况时都保留业务数据。

Backup is probably the simplest and the most familiar process in most situations. A backup is a copy of the data derived from the production copy, stored at a location that’s different from where the production data is stored. A backup copy is used to recover data needed to restart an application correctly, after certain types of failures. Using various tools and techniques, the data is replicated to different geographies and is made available all the time. Some may even argue that having multiple working copies of the data somewhere else removes the need for having backups, but in a not-so-perfect world, backups are still required to make sure you can always recover from data mishaps.

在大多数情况下，备份可能是最简单，最熟悉的过程。备份是从生产副本衍生的数据的副本，存储在与生产数据存储位置不同的位置。在某些类型的故障之后，备份副本用于恢复正确重新启动应用程序所需的数据。使用各种工具和技术，数据被复制到不同的地理位置，并始终可用。甚至有人可能会争辩说，在其他位置拥有多个数据工作副本将消除对备份的需求，但是在并非如此完美的世界中，仍然需要备份以确保您始终可以从数据灾难中恢复。

什么是备份？ (What is a backup?)

The word backup refers to copying and archiving the data so that it may be used to restore in case of an event of data loss. In general, you should back up any work or data that can’t be replaced easily.

备份一词指的是复制和归档数据，以便在发生数据丢失的情况下可以将其还原。通常，您应该备份所有无法轻松替换的工作或数据。

A database backup is a copy of data from the database, which can be used to reconstruct the data. A database backup is a process to protect the data from various disasters. Disasters cannot be completely prevented; the least we can do is to ensure we have everything that’s needed to get up and running as soon as we can. Database backup is also performed and managed to ensure that an organization complies with business and government regulations. For instance, for legal reasons in the US, correspondences should be saved for at least seven years in the manufacturing industry. The process of backing up data is pivotal to a successful disaster recovery (DR) plan.

数据库备份是数据库中数据的副本，可用于重建数据。数据库备份是一种保护数据免受各种灾难影响的过程。无法完全预防灾难；我们要做的至少是确保我们拥有尽快启动并运行所需的一切。还执行和管理数据库备份，以确保组织符合业务和政府法规。例如，出于法律原因，在美国，通信应在制造业中保存至少七年。数据备份过程对于成功的灾难恢复（DR）计划至关重要。

Organizations target for backup, the data they feel is vulnerable to:

组织的目标是备份，他们认为这些数据容易受到攻击：

Corruption
腐败
Failed hardware including media failure, software failure, and OS errors
硬件故障，包括介质故障，软件故障和操作系统错误
Attacks on the network or other malicious actions
对网络的攻击或其他恶意行为
Manual errors or other inadvertent actions
手动错误或其他意外操作
Unauthorized changes
未经授权的变更
and other undesirable events
和其他不良事件

我应该多久运行一次备份（备份频率）？ (How often should I run my backups (frequency of backups)?)

The frequency to run your backups depends on how often the data changes. You may find it helpful to consider the following points when planning your backup schedule:

运行备份的频率取决于数据更改的频率。在计划备份计划时，考虑以下几点可能会有所帮助：

Business data that changes frequently may need daily or hourly backups
经常更改的业务数据可能需要每日或每小时备份
Data that changes every few days could use a weekly or even monthly backup
每隔几天更改一次的数据可以使用每周甚至每月的备份
For some data, you might want to run a backup whenever you make a change
对于某些数据，您可能希望在进行更改时都运行备份

Predefined policies and Service Level Agreements (SLAs)govern the frequency and redundancy (or replicas) of the backup.

预定义的策略和服务级别协议（SLA）控制备份的频率和冗余（或副本）。

备份目的地 (Backup destination)

You can store backup files in one of the following locations:

您可以将备份文件存储在以下位置之一：

Local storage is used if you want to save the backup on a removable storage device attached to the computer or on a local computer drive. 本地存储被使用。
Shared folder is used if you want to save the backup in a network shared folder 夹中，则使用共享文件夹
Tape devices are used in case robust archival is required. 磁带设备 。
Cloud backup 云备份
- Public cloud storage: this type of storage is often used to backup the data as part of the Disaster Recovery where users send the data over to public cloud services such as S3. The providers generally charge based on monthly usage of storage. Some providers also charge for data transfers and access 公共云存储 ：此类型的存储通常用于备份数据，这是灾难恢复的一部分，用户将数据发送到诸如S3之类的公共云服务。提供商通常根据每月的存储使用量收费。一些提供商还对数据传输和访问收取费用
- Private cloud storage: is referred to as internal cloud storage. In this case, the data is backed up to servers within the environment, based on pre-determined set of policies and company’s firewall 私有云存储 ：称为内部云存储。在这种情况下，将根据预定的策略集和公司的防火墙将数据备份到环境中的服务器
- Hybrid cloud storage: is a combination of public and private cloud. 混合云存储 ：是公共云和私有云的组合。

谁应该量身定制您的备份 (Who should tailor your backup )

The backup process is handled by Backup Operators. They are specific individuals given the responsibility to configure, schedule, initiate and monitor the backup process.

备份过程由备份操作员处理。他们是负责配置，安排，启动和监视备份过程的特定人员。

了解RPO和RTO (Understanding RPO and RTO)

The RPO (Recovery-Point-Objective) and RTO (Recovery-Time-Objective) are vital parameters to consider when planning the backup strategy.

RPO（恢复点目标）和RTO（恢复时间目标）是计划备份策略时要考虑的重要参数。

The recovery point objective, or RPO, is the point in time that data can be restored to. For instance, if the most recent available backup is a week old, then we’d be able to restore to what the database looked like at that time the backup was taken, so you will potentially lose a week’s worth of data. On the other hand, if your most recent backup was taken five minutes ago, then we can restore to that point in time and would lose only five minutes of data. So the question would be how much data is the organization willing to lose—data worth hours, minutes, seconds or nothing at all? If the plan is zero data loss, then backup alone is not the solution. We need to use additional tools and techniques to achieve the desired result.

恢复点目标或RPO是可以将数据恢复到的时间点。例如，如果最近的可用备份已使用了一周，那么我们将能够还原到进行备份时数据库的外观，因此您可能会丢失一周的数据。另一方面，如果您最近的备份是在五分钟前进行的，那么我们可以还原到该时间点，并且只会丢失五分钟的数据。因此，问题将是组织愿意丢失多少数据-值得花费数小时，数分钟，数秒甚至什么都没有的数据？如果计划的数据丢失为零，那么单独备份不是解决方案。我们需要使用其他工具和技术来获得所需的结果。

The other term, recovery time objective, (or RTO) is the amount of time taken to perform the restoration. A very large database may take several hours or even a day to restore, which in some cases this may not be acceptable. Typically, running a restore takes slightly longer than running a backup, so if we do a backup which takes 30 minutes to run, it wouldn’t be surprising if the restore ran for longer than 30 minutes.

另一个术语恢复时间目标（或RTO）是执行恢复所花费的时间。一个非常大的数据库可能需要花费几个小时甚至一天来恢复，在某些情况下，这可能是不可接受的。通常，运行还原所花的时间要比运行备份所花的时间稍长，因此，如果执行的备份要花30分钟才能运行，那么还原运行超过30分钟也就不足为奇了。

摘要 (Summary )

As a general rule, the amount of time in between backups should be no more than the amount of time you are willing to spend redoing any lost work. For example, if spending a week recreating the lost data is too long for you, you should back up the data at least once a week.

通常，两次备份之间的时间不应超过您愿意花费在重做丢失的工作上的时间。例如，如果花一个星期来重新创建丢失的数据对您来说太长了，那么您应该每周至少备份一次数据。

The additional techniques that we mentioned for zero-loss model is called High Availability in most situations. These may be very expensive for some organizations. On the other hand, backups are more economical to run. Therefore, because of our budget constraints, sometimes we have to live with the fact that we will lose a few minutes of data.

我们在零损失模型中提到的其他技术在大多数情况下称为“高可用性”。对于某些组织而言，这些可能非常昂贵。另一方面，备份运行起来更经济。因此，由于预算限制，有时我们不得不忍受几分钟的数据丢失这一事实。

The recovery point objective and recovery time objective are metrics that are usually decided by the business. Several aspects of business are taken into consideration when coming up with these metrics. Once these objectives are decided, these must be clearly communicated to the stakeholders (IT and otherwise) within the organization.

恢复点目标和恢复时间目标是通常由业务决定的指标。提出这些指标时，需要考虑业务的几个方面。一旦确定了这些目标，就必须将这些目标清楚地传达给组织内的利益相关者（IT和其他方面）。