sqlserver-备份和还原

为何备份？

备份 SQL Server 数据库、在备份上运行测试还原过程以及在另一个安全位置存储备份副本可防止可能的灾难性数据丢失。 备份是保护数据的唯一方法 。

使用有效的数据库备份，可从多种故障中恢复数据，例如：

• 介质故障。
• 用户错误（例如，误删除了某个表）。
• 硬件故障（例如，磁盘驱动器损坏或服务器报废）。
• 自然灾难。 通过使用 SQL Server 备份到 Azure Blob
  存储服务，可以在本地位置之外的其他区域创建一个站外备份，这样在发生影响本地位置的自然灾难时仍可以使用数据库。

此外，数据库备份对于进行日常管理（如将数据库从一台服务器复制到另一台服务器、设置 Always On 可用性组 或数据库镜像以及进行存档）非常有用。

备份术语的术语表

数据备份 (data backup)
完整数据库的数据备份（数据库备份）、部分数据库的数据备份（部分备份）或一组数据文件或文件组的数据备份（文件备份）。

数据库备份 (database backup)
数据库的备份。 完整数据库备份表示备份完成时的整个数据库。 差异数据库备份只包含自最近完整备份以来对数据库所做的更改。

差异备份 (differential backup)
一种数据备份，基于完整数据库或部分数据库或一组数据文件或文件组（差异基准）的最新完整备份，并且仅包含自确定差异基准以来发生更改的数据。

完整备份 (full backup)
一种数据备份，包含特定数据库或者一组特定的文件组或文件中的所有数据，以及可以恢复这些数据的足够的日志。

日志备份 (log backup)
包括以前日志备份中未备份的所有日志记录的事务日志备份。 （完整恢复模式）

recover
将数据库恢复到稳定且一致的状态。

recovery
将数据库恢复到事务一致状态的数据库启动阶段或 Restore With Recovery 阶段。

恢复模式
用于控制数据库上的事务日志维护的数据库属性。 有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。 数据库的恢复模式确定其备份和还原要求。

还原 (restore)
一种包括多个阶段的过程，用于将指定 SQL Server 备份中的所有数据和日志页复制到指定数据库，然后通过应用记录的更改使该数据在时间上向前移动，以前滚备份中记录的所有事务。

备份和还原策略

备份和还原数据必须根据特定环境进行自定义，并且必须使用可用资源。 因此，要可靠地使用备份和还原进行恢复，需要制定备份和还原策略。设计完善的备份和还原策略可以平衡业务需求，以实现最大的数据可用性和最小的数据丢失，同时考虑维护和存储备份的成本。

备份和还原策略包含备份部分和还原部分。 策略的备份部分定义备份的类型和频率、备份所需硬件的特性和速度、备份的测试方法以及备份介质的存储位置和方法（包括安全注意事项）。 策略的还原部分定义负责执行还原的人员、如何执行还原以满足数据库可用性和最大程度减少数据丢失的目标，以及如何测试还原。

设计有效的备份和还原策略需要仔细计划、实现和测试。 需要进行测试：直到成功还原了还原策略中包含的所有组合中的备份并且测试了还原的数据库是否具有物理一致性后，才会生成备份策略。 必须考虑各种因素。 其中包括：

• 组织在生产数据库方面的目标，尤其是对可用性和防止数据丢失或损坏的要求。
• 每个数据库的特性包括：大小、使用模式、内容特性以及数据要求等。
• 对资源的约束，例如：硬件、人员、备份介质的存储空间以及所存储介质的物理安全性等。

最佳做法建议

使用独立的存储

重要

确保将数据库备份放在与数据库文件不同的物理位置或设备上。
存储数据库的物理驱动器出现故障或崩溃时，可恢复性取决于能否访问存储备份的独立驱动器或远程设备以执行还原。
请记住，你可以在同一个物理磁盘驱动器中创建多个逻辑卷或分区。 在为备份选择存储位置之前，请仔细研究磁盘分区和逻辑卷布局。

如何选择恢复模式

备份和还原操作发生在恢复模式的上下文中。 恢复模式是一种数据库属性，用于控制事务日志的管理方式。 因此，数据库的恢复模式决定了数据库支持的备份类型和还原方案，以及事务日志备份的大小。 通常，数据库使用简单恢复模式或完整恢复模式。 可以在执行大容量操作之前切换到大容量日志恢复模式，以补充完整恢复模式。 有关这些恢复模式以及它们是如何影响事务日志管理方式的说明，请参阅 事务日志 (SQL Server)。

数据库的最佳恢复模式取决于您的业务要求。 若要免去事务日志管理工作并简化备份和还原，请使用简单恢复模式。 若要在管理开销一定的情况下使工作丢失的可能性降到最低，请使用完整恢复模式。 为了在大容量日志操作期间最大程度减少对日志大小的影响，同时允许这些操作的可恢复性，请使用大容量日志恢复模式。 有关恢复模式对备份和还原的影响的信息，请参阅 备份概述 (SQL Server) 。

设计备份策略

当为特定数据库选择了满足业务要求的恢复模式后，需要计划并实现相应的备份策略。 最佳备份策略取决于各种因素，以下因素尤其重要：

• 一天中应用程序访问数据库的时间有多长？

  如果存在一个可预测的非高峰时段，则建议您将完整数据库备份安排在此时段。

• 更改和更新可能发生的频率如何？

  如果更改经常发生，请考虑下列事项：

  在简单恢复模式下，请考虑将差异备份安排在完整数据库备份之间。 差异备份只能捕获自上次完整数据库备份之后的更改。

  在完整恢复模式下，应安排经常的日志备份。 在完整备份之间安排差异备份可减少数据还原后需要还原的日志备份数，从而缩短还原时间。

• 可能只是更改数据库的小部分内容，还是需要更改数据库的大部分内容？

  对于更改集中于部分文件或文件组的大型数据库，部分备份和/或文件备份非常有用。 有关详细信息，请参阅 SQL Server) 部分备份 和
  完整文件 (备份 (SQL Server) 。

• 完整数据库备份需要多少磁盘空间？

• 你的企业需要维护过去多久的备份？

  确保你已根据应用程序需求和业务需求制定了适当的备份计划。 随着备份变得陈旧，数据丢失风险会更高，除非你有办法重新生成故障点之前的所有数据。
  由于存储资源限制而选择处理旧备份之前，请考虑是否需要以前的可恢复性

计划备份

执行备份操作对运行中的事务影响很小，因此可以在正常操作过程中执行备份操作。 您可以在对生产工作负荷的影响很小的情况下执行 SQL Server 备份。

有关备份期间并发限制的信息，请参阅 备份概述 (SQL Server) 。

确定所需的备份类型和必须执行每种备份类型的频率后，建议您将定期备份计划为数据库维护计划的一部分。 有关维护计划以及如何为数据库备份和日志备份创建维护计划的信息，请参阅 Use the Maintenance Plan Wizard。

备份操作限制

可以在数据库在线并且正在使用时进行备份。 但是，存在下列限制：

无法备份脱机数据

隐式或显式引用脱机数据的任何备份操作都会失败。 一些典型示例包括：

• 您请求完整数据库备份，但是数据库的一个文件组脱机。 由于所有文件组都隐式包含在完整数据库备份中，因此，此操作将会失败。

  若要备份此数据库，可以使用文件备份并仅指定联机的文件组。

• 请求部分备份，但是有一个读/写文件组处于脱机状态。 由于部分备份需要使用所有读/写文件组，因此该操作失败。

• 请求特定文件的文件备份，但是其中有一个文件处于脱机状态。 该操作失败。 若要备份联机文件，可以省略文件列表中的脱机文件并重复该操作。

通常，即使一个或多个数据文件不可用，日志备份也会成功。 但如果某个文件包含大容量日志恢复模式下所做的大容量日志更改，则所有文件都必须都处于联机状态才能成功备份。

并发限制

SQL Server 可以使用联机备份过程来备份数据库。 在备份过程中，可以进行多个操作；例如：在执行备份操作期间允许使用 INSERT、UPDATE 或 DELETE 语句。 但是，如果在正在创建或删除数据库文件时尝试启动备份操作，则备份操作将等待，直到创建或删除操作完成或者备份超时。

在数据库备份或事务日志备份的过程中无法执行的操作包括：

• 文件管理操作，如含有 ADD FILE 或 REMOVE FILE 选项的 ALTER DATABASE 语句。
• 收缩数据库或文件操作。 这包括自动收缩操作。
• 如果在进行备份操作时尝试创建或删除数据库文件，则创建或删除操作将失败。

如果备份操作与文件管理操作或收缩操作重叠，则产生冲突。 无论哪个冲突操作首先开始，第二个操作总会等待第一个操作设置的锁超时。（超时期限由会话超时设置控制。）如果在超时期限内释放锁，第二个操作将继续执行。 如果锁超时，则第二个操作失败。

测试备份！

直到完成备份测试后，才会生成还原策略。 必须通过将数据库副本还原到测试系统，针对每个数据库的备份策略进行全面测试。 您必须对每种要使用的备份类型进行还原测试。 另外建议在还原备份后，通过数据库的 DBCC CHECKDB 执行数据库一致性检查，以验证备份媒体是否未损坏。

使用 Transact-SQL 创建维护计划

在 “对象资源管理器” 中，连接到 数据库引擎的实例。

在标准菜单栏上，单击 “新建查询” 。

将以下示例复制并粘贴到查询窗口中，然后单击“执行” 。

USE msdb;  
GO  
--  Adds a new job, executed by the SQL Server Agent service, called "HistoryCleanupTask_1".  
EXEC dbo.sp_add_job  
   @job_name = N'HistoryCleanupTask_1',   
   @enabled = 1,   
   @description = N'Clean up old task history' ;   
GO  
-- Adds a job step for reorganizing all of the indexes in the HumanResources.Employee table to the HistoryCleanupTask_1 job.   
EXEC dbo.sp_add_jobstep  
    @job_name = N'HistoryCleanupTask_1',   
    @step_name = N'Reorganize all indexes on HumanResources.Employee table',   
    @subsystem = N'TSQL',   
    @command = N'USE AdventureWorks2012  
GO  
ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX AK_Employee_NationalIDNumber ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX AK_Employee_rowguid ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee REORGANIZE WITH ( LOB_COMPACTION = ON )   
GO  
USE AdventureWorks2012  
GO  
ALTER INDEX PK_Emplo