SQL Server的备份

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介:

0.参考文献

1.恢复模式

SQL Server 备份和还原操作发生在数据库的恢复模式的上下文中。 恢复模式旨在控制事务日志维护。 “恢复模式”是一种数据库属性,它控制如何记录事务,事务日志是否需要(以及允许)备份,以及可以使用哪些类型的还原操作。 有三种恢复模式:简单恢复模式、完整恢复模式和大容量日志恢复模式。通常,数据库使用完整恢复模式或简单恢复模式。可以在执行大容量操作之前切换到大容量日志恢复模式,以补充完整恢复模式。数据库可以随时切换为其他恢复模式。

1.1恢复模式概述

下表概述了这三种恢复模式。

 

恢复模式

说明

工作丢失的风险

能否恢复到时点?

简单(SIMPLE)

无日志备份。

自动回收日志空间以减少空间需求,实际上不再需要管理事务日志空间。 有关简单恢复模式下数据库备份的信息,请参阅完整数据库备份 (SQL Server)

最新备份之后的更改不受保护。 在发生灾难时,这些更改必须重做。

只能恢复到备份的结尾。 有关详细信息,请参阅完整数据库还原(简单恢复模式)

完全(FULL)

需要日志备份。

数据文件丢失或损坏不会导致丢失工作。

可以恢复到任意时点(例如应用程序或用户错误之前)。 有关完整恢复模式下数据库备份的信息,请参阅完整数据库备份 (SQL Server)完整数据库还原(完整恢复模式)

正常情况下没有。

如果日志尾部损坏,则必须重做自最新日志备份之后所做的更改。

如果备份在接近特定的时点完成,则可以恢复到该时点。 有关使用日志备份还原到故障点的信息,请参阅将 SQL Server 数据库还原到某个时点(完整恢复模式).

注意 注意

如果有两个或更多必须在逻辑上保持一致的完整恢复模式数据库,则最好执行特殊步骤,以确保这些数据库的可恢复性。 有关详细信息,请参阅包含标记的事务的相关数据库的恢复

大容量日志(BULK_INSERT)

需要日志备份。

是完整恢复模式的附加模式,允许执行高性能的大容量复制操作。

通过使用最小方式记录大多数大容量操作,减少日志空间使用量。 有关尽量减少日志量的操作的信息,请参阅事务日志 (SQL Server)

有关大容量日志恢复模式下数据库备份的信息,请参阅完整数据库备份 (SQL Server) 和完整数据库还原(完整恢复模式)

如果在最新日志备份后发生日志损坏或执行大容量日志记录操作,则必须重做自该上次备份之后所做的更改。

否则不丢失任何工作。

可以恢复到任何备份的结尾。 不支持时点恢复。

查看或更改数据库的恢复模式

  1. 连接到相应的 Microsoft SQL Server 数据库引擎 实例之后,在对象资源管理器中,单击服务器名称以展开服务器树。
  2. 展开“数据库”,然后根据数据库的不同,选择用户数据库,或展开“系统数据库”,再选择系统数据库。
  3. 右键单击该数据库,再单击“属性”,这将打开“数据库属性”对话框。
  4. 在“选择页”窗格中,单击“选项”。
  5. 当前恢复模式显示在“恢复模式”列表框中。
  6. 也可以从列表中选择不同的模式来更改恢复模式。可以选择“完整”、“大容量日志”或“简单”。如下图所示:

 1.3.使用TSQL更改数据库恢复模式

复制代码
--更改数据库恢复模式
alter database AdventureWorks2012 set recovery {FULL|SIMPLE|BULK_LOGGED}
--简单恢复模式
alter database AdventureWorks2012 set recovery SIMPLE
--完整恢复模式
alter database AdventureWorks2012 set recovery BULK_LOGGED
--大容量日志恢复模式
alter database AdventureWorks2012 set recovery FULL
复制代码

1.4.估计完整数据库备份的大小

  在实现备份与还原策略之前,应当估计完整数据库备份将使用的磁盘空间。 备份操作会将数据库中的数据复制到备份文件。 备份仅包含数据库中的实际数据,而不包含任何未使用的空间。 因此,备份通常小于数据库本身。(这也是为什么完整数据库备份比文件备份更加节省空间的原因。) 您可以使用 sp_spaceused系统存储过程估计完整数据库备份的大小。 有关详细信息,请参阅 sp_spaceused (Transact-SQL)

2.为磁盘文件定义逻辑备份设备

2.1.限制和局限

逻辑设备名称在服务器实例上的所有逻辑备份设备中必须是唯一的。 若要查看现有逻辑设备名称,请查询sys.backup_devices 目录视图。

2.2.建议

我们建议备份磁盘应不同于数据库数据和日志的磁盘。 这是数据或日志磁盘出现故障时访问备份数据必不可少的。

2.3使用 SQL Server Management Studio为磁盘文件定义逻辑备份设备

  1. 连接到相应的 Microsoft SQL Server 数据库引擎实例之后,在对象资源管理器中,单击服务器名称以展开服务器树。
  2. 展开“服务器对象”,然后右键单击“备份设备”。
  3. 单击“新建备份设备”。 将打开“备份设备”对话框。
  4. 输入设备名称。
  5. 若要确定目标位置,请单击“文件”并指定该文件的完整路径。
  6. 若要定义新设备,请单击“确定”。

若要备份至新设备,右键设备名称,选择"back up a database",然后再具体的对话框中选择需要备份的数据库。如下图所示:

2.4.使用 Transact-SQL为磁盘文件定义逻辑备份

复制代码
--查询备份设备
select * from  sys.backup_devices;
--定义磁盘备份设备
EXEC sp_addumpdevice 'disk', 'mybackupdisk', 'd:\backup\backup1.bak' ;
--删除磁盘备份设备
EXEC sp_dropdevice 'mybackupdisk', 'delfile' ;
复制代码

3.创建完整数据库备份 (SQL Server)

关于完整备份的点(PS:2012-7-17)

问题:sql server从2点开始备份,4点备份完。那么进行完整还原的时候,恢复到的是哪一个时间点。是2点,还是4点,或者是其他时间点。

解答:首先,恢复到的是4点。这是因为在进行full database backup的时候,会有一个开始备份的LSNs,在full database backup 完成的时候,又有一个备份完成的LSNe。在备份完成以后,数据库会redo从LSNa到LSNb这一段log record。所以回复到的是4点。

执行如下命令

dbcc log(TESTDB3,3)
checkpoint
backup database TESTDB3 to disk='d:\backup\backup3.bak'
dbcc log(TESTDB3,3)

查询结果如下所示:

这说明进行backup的时候会被记录到log record中。

3.1.限制和局限

  • 不允许在显式或隐式事务中使用 BACKUP 语句。

  • 无法在早期版本的 SQL Server 中还原较新版本的 SQL Server 创建的备份。

3.2.建议

  • 随着数据库不断增大,完整备份需花费更多时间才能完成,并且需要更多的存储空间。 因此,对于大型数据库而言,您可以用一系列“差异数据库备份”来补充完整数据库备份。 有关详细信息,请参阅差异备份 (SQL Server)

  • 您可以使用 sp_spaceused 系统存储过程估计完整数据库备份的大小。

  • 默认情况下,每个成功的备份操作都会在 SQL Server 错误日志和系统事件日志中添加一个条目。 如果非常频繁地备份日志,这些成功消息会迅速累积,从而产生一个巨大的错误日志,这样会使查找其他消息变得非常困难。 在这些情况下,如果任何脚本均不依赖于这些日志条目,则可以使用跟踪标志 3226 取消这些条目。 有关详细信息,请参阅跟踪标志 (Transact-SQL)

3.3.权限

默认情况下,为 sysadmin 固定服务器角色以及 db_owner 和 db_backupoperator 固定数据库角色的成员授予 BACKUP DATABASE 和 BACKUP LOG 权限。

备份设备的物理文件的所有权和权限问题可能会妨碍备份操作。 SQL Server 必须能够读取和写入设备;运行 SQL Server 服务的帐户必须具有写入权限。 但是,用于在系统表中为备份设备添加项目的 sp_addumpdevice 不检查文件访问权限。 备份设备物理文件的这些问题可能直到为备份或还原而访问物理资源时才会出现。

3.4.使用 SQL Server Management Studio备份数据库

右键数据库AdventureWorks2012->tasks->back up,如下图所示:

完全备份配置选项

  1. backup type:full,表示完全备份
  2. backup component:database
  3. backup set->name:默认
  4. backup set will expire:after:0,表示永远不失效。
  5. destination:disk,表示备份到磁盘
  6. 点击add选择备份设备或者指定备份路径,如下图所示:
  7. 我们这里选择的是前面创建的backup device:mybackupdisk。
  8. 注意:我们可以指定多个backup device。指定多个备份设备可以节约备份时间。并行写入。

验证

完成上述配置以后并确定备份,我们就可以在磁盘目录”d:\backup\backup1.bak"下找到我们的备份文件,一共有189MB。

3.5.使用 Transact-SQL创建完整数据库备份

复制代码
--默认情况下,BACKUP DATABASE 创建完整备份。
--定义备份设备
EXEC sp_addumpdevice 'disk', 'mybackupdisk2', 'd:\backup\backup2.bak';
--备份到逻辑设备
BACKUP DATABASE AdventureWorks2012 TO mybackupdisk2
   WITH NOINIT,NAME = 'Full Backup of AdventureWorks2012';
--直接备份到磁盘
BACKUP DATABASE AdventureWorks2012 TO disk='d:\backup\backup3.bak'
   WITH NOINIT,NAME = 'Full Backup of AdventureWorks2012';
复制代码

BACKUP具体语法参考BACKUP (Transact-SQL)

4.差异数据库备份

4.1.必备条件

  创建差异数据库备份需要有以前的完整数据库备份。 如果选定的数据库从未进行过备份,则请在创建任何差异备份之前,先执行完整数据库备份。 有关详细信息,请参阅创建完整数据库备份 (SQL Server)

4.2.建议

  当差异备份的大小增大时,还原差异备份会显著延长还原数据库所需的时间。 因此,建议按设定的间隔执行新的完整备份,以便为数据建立新的差异基准。 例如,您可以每周执行一次整个数据库的完整备份(即完整数据库备份),然后在该周内执行一系列常规的差异数据库备份。

4.3.使用 SQL Server Management Studio创建差异数据库备份

操作步骤跟3.4节完整备份数据库一样,只是将buckup type类型改成Differential而已。destination可以依然选择完整备份的那一个device,不过要求是NOINIT,而不能是INIT,因为如果是INIT的话会覆盖原来的完整备份。

4.4.使用 Transact-SQL创建差异数据库备份

复制代码
USE [TSQL2012]
--定义备份设备
EXEC sp_addumpdevice 'disk', 'backupdevice1', 'd:\backup\backup_tsql2012.bak';

--完整备份数据库
BACKUP DATABASE TSQL2012 TO backupdevice1 WITH NOINIT,NAME = 'Full Backup of TSQL2012';

--插入数据
INSERT INTO dbo.test(OrderID,ProductID) VALUES(1,1);
INSERT INTO dbo.test(OrderID,ProductID) VALUES(2,2);
INSERT INTO dbo.test(OrderID,ProductID) VALUES(3,3);

--差异备份数据库
BACKUP DATABASE TSQL2012 TO backupdevice1 WITH DIFFERENTIAL,NOINIT,NAME = 'DIFFERENTIAL Backup of TSQL2012';
复制代码

验证我们创建的完整备份和差异备份

右键选择数据库TSQL2012->tasks->restore->database,出现如下图所示内容:

如上图所示,出现了我们之前创建的完整备份和差异备份。

5.事务日志备份

5.1.建议

  • 如果数据库使用完整恢复模式或大容量日志恢复模式,则必须足够频繁地备份事务日志,以保护数据和避免事务日志变满。 这将截断日志,并且支持将数据库还原到特定时间点。

  • 默认情况下,每个成功的备份操作都会在 SQL Server 错误日志和系统事件日志中添加一个条目。 如果非常频繁地备份日志,这些成功消息会迅速累积,从而产生一个巨大的错误日志,这样会使查找其他消息变得非常困难。 在这些情况下,如果任何脚本均不依赖于这些日志条目,则可以使用跟踪标志 3226 取消这些条目。 有关详细信息,请参阅跟踪标志 (Transact-SQL)

5.2.使用 SQL Server Management Studio创建差异数据库备份

5.2.1.例行事务日志的备份

5.2.2.尾部事务日志的备份

5.3.使用 Transact-SQL创建差异数据库备份

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
5天前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
2月前
|
SQL 数据库
Microsoft SQL Server 2014如何来备份数据库
Microsoft SQL Server 2014如何来备份数据库
105 3
|
2月前
|
SQL 存储 数据库
备份SQL语句
【8月更文挑战第20天】备份SQL语句
29 3
|
2月前
|
SQL 数据库
SQL Server 如何进行备份?
【8月更文挑战第20天】SQL Server 如何进行备份?
32 3
|
2月前
|
SQL 存储 数据库
SQL Server 中的备份类型详解
【8月更文挑战第31天】
44 0
|
2月前
|
数据库 Java 监控
Struts 2 日志管理化身神秘魔法师,洞察应用运行乾坤,演绎奇幻篇章!
【8月更文挑战第31天】在软件开发中,了解应用运行状况至关重要。日志管理作为 Struts 2 应用的关键组件,记录着每个动作和决策,如同监控摄像头,帮助我们迅速定位问题、分析性能和使用情况,为优化提供依据。Struts 2 支持多种日志框架(如 Log4j、Logback),便于配置日志级别、格式和输出位置。通过在 Action 类中添加日志记录,我们能在开发过程中获取详细信息,及时发现并解决问题。合理配置日志不仅有助于调试,还能分析用户行为,提升应用性能和稳定性。
38 0
|
2月前
|
SQL 安全 测试技术
【数据守护者必备】SQL数据备份与恢复策略全解析:从全量到日志备份,手把手教你确保企业信息万无一失的实战技巧!
【8月更文挑战第31天】数据库是企业核心业务数据的基石,为防止硬件故障、软件错误或人为失误导致的数据丢失,制定可靠的备份与恢复策略至关重要。本文通过一个在线购物平台的案例,详细介绍了使用 SQL Server 进行全量备份、差异备份及事务日志备份的方法,并演示了如何利用 SQL Server Agent 实现自动化备份任务。此外,还提供了数据恢复的具体步骤和测试建议,确保数据安全与业务连续性。
47 0
|
3月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
67 13
|
3月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。
|
3月前
|
SQL 存储 网络安全
关系数据库SQLserver 安装 SQL Server
【7月更文挑战第26天】
47 6
下一篇
无影云桌面