什么是SQL Server灾难恢复中的备份和还原？

定义SQL Server备份和还原 (Defining SQL Server backup and restore)

SQL Server备份和还原技术涉及创建备份，还原数据库以及将有效备份副本保存在其他位置的步骤。 它是SQL Server的内置功能，可用于灾难恢复计划。

The backup and restore process in SQL Server involves three main steps:

SQL Server中的备份和还原过程涉及三个主要步骤：

1. Backing up the desired SQL Server database
   备份所需SQL Server数据库
2. Transferring the backup files to another location, usually a remote location to avoid risk of a hard drive failure along with possible software problems (e.g. virus attacks)
   将备份文件传输到另一个位置，通常是一个远程位置，以避免硬盘故障以及可能的软件问题（例如病毒攻击）的风险
3. Restoring the database on SQL Server to ensure that the database backup is valid and to verify integrity.
   在SQL Server上还原数据库，以确保数据库备份有效并验证完整性。

Restoring a database backup will ensure that in case of a disaster, the restore process will be successful with no errors or problems in general. The SQL Server backup and restore process requires constant testing of backed up files to provide reliable disaster recovery plan. If the backups are not being tested, they might turn out to be bad files that can’t be used for data recovery.

还原数据库备份将确保在发生灾难的情况下，还原过程将成功完成，并且通常不会出现任何错误或问题。 SQL Server备份和还原过程要求对备份文件进行不断测试，以提供可靠的灾难恢复计划。 如果未对备份进行测试，则它们可能是无法用于数据恢复的错误文件。

实施实例 (Implementation examples)

One of the common backup and restore configurations is an environment with two SQL Servers (SQLServer-1 and SQLServer-2), two SQL Server instances (SQLInstance-1 and SQLInstance-2), and one database named SQLDB-1 which will be backed up, transferred, and restored to SQLServer-2.

常见的备份和还原配置之一是具有两个SQL Server（SQLServer-1和SQLServer-2），两个SQL Server实例（SQLInstance-1和SQLInstance-2）以及一个将被备份的名为SQLDB-1的数据库的环境。 ，传输和还原到SQLServer-2。

The second common configuration is an environment with one SQL Server (SQLServer-1), two SQL Server instances (SQLServer-1 and SQLServer-2), and one database named SQLDB-1. The major disadvantage of this configuration is that if SQLServer-1 goes down or there is a hardware error, both database copies will be unavailable. This solution is acceptable if there is a need for testing the copy of the database SQLDB-1.

第二种常见配置是具有一个SQL Server（SQLServer-1），两个SQL Server实例（SQLServer-1和SQLServer-2）以及一个名为SQLDB-1的数据库的环境。 此配置的主要缺点是，如果SQLServer-1发生故障或出现硬件错误，则两个数据库副本均将不可用。 如果需要测试数据库SQLDB-1的副本，则此解决方案是可以接受的。

操作模式 (Operating modes)

SQL Server has several backup options available, the most important are:

SQL Server有几个可用的备份选项，最重要的是：

Database backup – A database backup that can be full or differential. A Full database backup contains all data in the specific database and enough transaction log data to allow recovery. A Differential database backup contains only the data that has been changed since the last full database backup. A differential database backup requires a prior full database backup.

数据库备份 –可以是完整备份或差异备份的数据库备份。 完整数据库备份包含特定数据库中的所有数据以及足够的事务日志数据以允许恢复。 差异数据库备份仅包含自上次完整数据库备份以来已更改的数据。 差异数据库备份需要事先进行完整的数据库备份。

The second important backup option is Transaction log backup. A transaction log backup contains all transaction log records that were not backed up. It’s available only in Full and Bulk logged recovery models.

第二个重要的备份选项是“ 事务日志备份” 。 事务日志备份包含所有未备份的事务日志记录。 仅在完整和批量记录的恢复模型中可用。

恢复模型 (Recovery models)

SQL Server comes with three different recovery models. The SQL Server recovery model determinates how the transaction log is maintained.

SQL Server带有三种不同的恢复模型。 SQL Server恢复模型确定了如何维护事务日志。

The Simple recovery model is used generally for the test and development databases and also for data warehouses with mostly read-only data. The transaction log is self-maintained and there is little chance for the transaction log growing. Old and backed up transactions are marked for reuse and will be overwritten with new ones automatically. There is a possibility for data loss due to automatic deletion of old transactions. Changes after the most recent database backup are exposed. In the event of a disaster, all changes made to a database after the last backup, must be redone. There is no option for a point-in-time recovery; data can be recovered only to the end of a backup. Transaction log backups cannot be created.

简单恢复模型通常用于测试和开发数据库，​​也用于具有大部分只读数据的数据仓库。 事务日志是自维护的，事务日志增长的可能性很小。 旧的和备份的事务被标记为可重复使用，并将自动被新的事务覆盖。 由于自动删除旧交易，可能会导致数据丢失。 公开了最近一​​次数据库备份之后的更改。 万一发生灾难，必须重做上次备份后对数据库所做的所有更改。 无法进行时间点恢复； 数据只能恢复到备份结束。 无法创建事务日志备份。

The Bulk logged recovery model is used for production systems where there is a need for large bulk operations such as index creation or bulk import. It minimally logs most of bulk operations, therefore saves the amount of needed space in the transaction log file. There is a possibility for data loss if the transaction log is damaged or bulk logged operations occurred since the most recent transaction log backup. There is no option for point-in-time recovery; data can be recovered to the end of the backup. Transaction log backups are necessary for minimal data loss.

批量记录的恢复模型用于需要大型批量操作（例如索引创建或批量导入）的生产系统。 它最少地记录了大多数批量操作，因此节省了事务日志文件中所需的空间量。 如果自从最近一次事务日志备份以来，事务日志已损坏或发生了批量记录的操作，则可能会丢失数据。 没有时间点恢复选项。 数据可以恢复到备份结束。 为了最小化数据丢失，必须进行事务日志备份。

The Full recovery model is used for productions systems where no data loss is allowed. All transactions are being logged into the transaction log file. When a transaction log file backup is created, the backed up transactions will be marked for reuse. In the Full recovery model, there is the biggest chance of transaction log growth because all transactions are logged, and not removed until a transaction log backup is created. Because of that, taking backups on regular basis is highly recommended. There is no data loss exposure. There must be a full chain of transaction log backups present in order for data to be recovered. If the tail of the transaction log is damaged, the changes that occurred after the last transaction log backup will be lost. A point-in-time recovery is possible if all backups are available up to that point in time. Transaction log backups are necessary for minimal data loss. The transaction log backups must be created regularly to maintain the online transaction log size.

完全恢复模型用于不允许数据丢失的生产系统。 所有事务都被记录到事务日志文件中。 创建事务日志文件备份后，已备份的事务将被标记为可重复使用。 在完全恢复模型中，事务日志增长的机会最大，因为所有事务都已记录，并且只有在创建事务日志备份后才会删除。 因此，强烈建议定期进行备份。 没有数据丢失风险。 必须有完整的事务日志备份链才能恢复数据。 如果事务日志的尾部损坏，则上次事务日志备份之后发生的更改将丢失。 如果所有备份在该时间点之前都可用，则可以进行时间点恢复。 为了最小化数据丢失，必须进行事务日志备份。 必须定期创建事务日志备份，以维护联机事务日志的大小。

使用SQL Server备份和还原的优缺点 (Advantages and disadvantages of using SQL Server backup and restore)

Using SQL Server backup and restore has multiple benefits:

使用SQL Server备份和还原有多个好处：

• simple implementation
  简单的实现
• ability to be scripted and scheduled
  可以编写脚本和安排时间
• ability to save copies of the on multiple locations
  可以在多个位置保存副本的功能
• inexpensive
  便宜的
• maintained current copy of data.
  维护当前数据副本。

Since SQL Server version 2008, backups can be natively compressed which results in less space and time needed for creating a backup.

从SQL Server 2008版开始，可以对备份进行本地压缩，从而减少了创建备份所需的空间和时间。

The backup and restore process has some disadvantages: it’s not possible to automatically backup and restore a database on a different SQL Server instance and it’s necessary to have a database in the Full recovery model.

备份和还原过程有一些缺点：无法在其他SQL Server实例上自动备份和还原数据库，并且必须在完全恢复模型中建立数据库。

设置数据库备份和还原过程 (Setting up the database backup and restore process)

A SQL Server database can be backed up and restored via the SQL Server Management Studio wizard or T-SQL.

可以通过SQL Server Management Studio向导或T-SQL备份和还原SQL Server数据库。

T-SQL for backing up the whole ACMEDB database:

使用T-SQL备份整个ACMEDB数据库：

 
BACKUP DATABASE ACMEDB 
 TO DISK = 'D:\Backups\ACMEBackupData.bak'
   WITH FORMAT;
GO
    

T-SQL for backing up the transaction log of the ACMEDB database:

使用T-SQL备份ACMEDB数据库的事务日志：

 
BACKUP LOG ACMEDB
   TO DISK = 'D:\Backups\ACMEBackupData.bak'
GO
    

T-SQL for restoring the ACMEDB database:

用于还原ACMEDB数据库的T-SQL：

 
RESTORE DATABASE ACMEDB
   FROM DISK = 'D:\Backups\ACMEBackupData.bak'
GO
    

The SQL Server Management Studio wizard offers options through the Back Up dialog.

SQL Server Management Studio向导通过“ 备份”对话框提供选项。

In the Back Up Database dialog, the user can choose the source and backup type, along with the name of the backup set.

在“ 备份数据库”对话框中，用户可以选择源和备份类型，以及备份集的名称。

The restore process can also be managed through the wizard. If the full recovery model is used, there is also an option for choosing a specific point in time to restore the data.

还可以通过向导管理还原过程。 如果使用完整恢复模型，则还可以选择一个特定的时间点来还原数据。

翻译自: https://www.sqlshack.com/backup-and-restore-sql-server-disaster-recovery/