数据库备份那点事儿

一. 概述

  文件备份是指备份一个或多个文件或文件组中的所有数据。使用文件备份能够只还原损坏的文件,而不用还原数据库的其余部份,从而加快恢复速度。例如,如果数据库由位于不同磁盘上的若干文件组成,在其中一个磁盘发生故障时,只需还原这个故障磁盘上的文件,其它磁盘文件无须还原,这样缩短还原时间。

  在完整恢复模式下,一整套完整文件备份和涵盖所有文件备份的日志备份合起来,等同于一个完整数据库备份。

  1.1 文件备份具有如下优点:
    (1)可以迅速还原损坏的文件。
    (2)当超大型数据库在完整备份下变得难以管理时,文件备份增加了计划和媒体处理的灵活性。

  1.2 文件备份具有不足:
    (1)
与完整数据库备份相比,文件备份的主要缺点是管理较为复杂。如果某个损坏的文件未备份,那么媒体故障可能会导致无法恢复整个数据库。因此必须维护一组完整的文件备份,还必须维护一个或多个日志备份。
    (2)
维护和跟踪这些完整备份是一种耗时的任务,所需空间会超过完整数据库备份所需的空间。

 使用压缩备份

  数据库往往比较大,那么同样备份文件占用的空间也很大,由于常常要保留几天甚至一周的数据在本地磁盘,压缩备份可以极大的减少备份文件对磁盘空间的占用。同时因为文件小了,备份产生IO的压力也会降低,但会对消耗比较多的CPU。

  图片 1

 

四 . 数据初始化  

--第一步: 创建数据库
CREATE DATABASE [FileGroupTest]
go
USE [FileGroupTest]

--第二步:创建文件组
ALTER DATABASE [FileGroupTest] ADD FILEGROUP [FG_Test_Id_01]
ALTER DATABASE [FileGroupTest] ADD FILEGROUP [FG_Test_Id_02]

--第三步:创建文件添加到文件组
ALTER DATABASE [FileGroupTest] ADD FILE
(NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'D:DataFG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_01]

ALTER DATABASE [FileGroupTest] ADD FILE
(NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'D:DataFG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_02]

--第四步创建表存放在不同文件上
CREATE TABLE  Student(ID INT,Name varchar(50),[Address] varchar(100)) ON [FG_Test_Id_01]
CREATE TABLE  Teacher(ID INT,Name varchar(50),[Address] varchar(100)) ON [FG_Test_Id_02]
CREATE TABLE  School(ID INT,Name varchar(50),[Address] varchar(100)) ON [PRIMARY]

图片 2

图片 3

-- 养成好习惯先进行完整备份
backup database  [FileGroupTest] to BackupTestDevice

写在前面

  最近一直在整理数据库最佳实践的东西,我也会将各种文章建议,同步到博客园,希望能够帮助更多的人了解数据库,轻松玩转数据库,同时也减轻运维人员的工作压力,毕竟熟能生巧,熟练既是效率。

  数据库备份老生常谈的话题,一搜索数据库备份可能上千上万篇,那么为什么还要写一篇?因为重要!而往往却不能引起运维人员的重视。上周还帮助一个客户恢复了数据,原因是断电,启动服务器后发现磁盘损坏,重要的系统页大面积损坏。使用常规数据库恢复手段全无用,使用第三方恢复工具也只能恢复部分数据,根本无法满足业务的正常运转,数据是企业的命根子,丢了,找不回来怎么办?
难道要经历一次这样的洗礼才能体会到备份的重要性么?

  数据库备份是个很重的话题,太多东西无法写在同一篇文章中,另外这是一篇大量文字的扫盲文章,不足之处希望大家多多包涵。

三.文件还原  

   当一个大数据库有若干个文件和文件组,如果损坏只是集中在其中一个文件或文件组上,sqlserver只要把坏掉的那个数据文件组重建,肯定可以节约时间。但是数据库的事务修改是会分布在各个数据文件上的,如果用备份只恢复其中一个文件,而其它文件不恢复,那么它们的状态一定会不一致,这样数据库是无法使用的,为了使新恢复的文件能够自动恢复备份以后做的修改,就需要借助事务日志。使用文件备份还原一个或多个受损文件的步骤如下:

  (1) 创建活动事务日志的尾日志备份。
对于离线文件还原,在文件还原之前必须始终先进行一次尾日志备份。对于在线文件还原,在文件还原之后必须始终先进行一次日志备份。因为日志文件一日损坏,文件还原则无法进行。

  (2) 从每个损坏的文件的最新文件备份还原相应文件。

  (3)
针对每个还原的文件,还原最近的差异文件备份(如果有,因为这样还原快)

  (4)
按顺序还原事务日志备份,从时间上最早备份的日志文件开始,到步骤1的尾日志结束。

简单与完整模式下的备份详细描述

  简单恢复模式下的备份

  简单恢复模式是最简单的备份和还原形式。该恢复模式同时支持数据库备份和文件备份,但不支持日志备份。事务日志数据仅与关联的用户数据一起备份。缺少日志备份可简化备份和还原的管理。但是,数据库只能还原到最近备份的末尾。

  下图显示了简单恢复模式下最简单的备份与还原策略。此策略仅使用包含数据库中所有数据的完整数据库备份。存在五个完整数据库备份,但只需要还原最近的备份(在
t5 时点执行的备份)。还原此备份会将数据库恢复到 t5 时点。由 t6
框表示的所有后续更新都将丢失。

图片 4

 

 

  最大程度地降低工作丢失的风险

 

  在简单恢复模式下,在执行下次完整备份或差异备份前,所做工作丢失的风险会随时间的推移而增加。与完整备份不同的是,差异备份仅包括自上次完整备份以来所做的更改。因此,我们建议您在不影响备份管理的前提下时常备份,以免丢失大量数据。

下图显示了仅使用数据库备份的备份计划的工作丢失风险。此策略仅适用于可经常备份的小型数据库。

图片 5

下图显示的备份策略通过使用差异数据库备份对数据库备份进行补充,从而减少了工作丢失风险。在第一个数据库备份完成后,会接着进行三个差异数据库备份。第三个差异备份足够大,因而下一个备份为完整数据库备份。该数据库备份将成为新的差异基准。

图片 6

 

 

  在完整恢复模式下备份

  完整恢复模式使用日志备份在最大范围内防止出现故障时丢失数据,这种模式需要备份和还原事务日志(“日志备份”)。使用日志备份的优点是允许您将数据库还原到日志备份中包含的任何时点(“时点恢复”)。可以使用一系列日志备份将数据库前滚到其中一个日志备份中包含的任意时点。请注意,为了最大程度地缩短还原时间,可以对相同数据进行一系列差异备份以补充每个完整备份。

假定可以在发生严重故障后备份活动日志,则可将数据库一直还原到没有发生数据丢失的故障点处。使用日志备份的缺点是它们需要使用存储空间并会增加还原时间和复杂性。

 

  下图显示了在完整恢复模式下的最简单的备份策略。在此图中,已完成了完整数据库备份
Db_1 以及两个例行日志备份 Log_1 和 Log_2。在 Log_2
日志备份后的某个时间,数据库出现数据丢失。在还原这三个备份前,数据库管理员必须备份活动日志(日志尾部)。然后还原
Db_1、Log_1 和
Log_2,而不恢复数据库。接着数据库管理员还原并恢复结尾日志备份
(Tail)。这将把数据库恢复到故障点,从而恢复所有数据。

图片 7

 

  最大程度地降低工作丢失的风险

 

 在第一个完整数据库备份完成并且常规日志备份开始之后,潜在的工作丢失风险的存在时间仅为数据库损坏时以及执行最新的常规日志备份时。因此,建议经常执行日志备份,以将工作丢失的风险限定在业务要求所允许的范围内。

下图显示的备份策略使用差异数据库备份来补充完整数据库备份和日志备份。事务日志备份可缩短潜在的工作丢失风险的存在时间,使该风险仅在最新日志备份
t14
之后存在。进行一系列差异备份(三次备份)来减少在出现故障时需要还原的事务日志数。第三个差异备份很大,足以使下一个备份成为完整数据库备份。该数据库备份将成为新的差异基准。

图片 8

在此图中的第一个数据库备份创建之前,数据库存在潜在的工作丢失风险(从时间
t0 到时间
t1)。该备份建立之后,例行日志备份将工作丢失的风险降为丢失自最近日志备份之后所做的更改(在此图中,最近备份的时间为
t14)。如果在最新备份后出现故障,数据库管理员将尝试备份日志尾部(尚未备份的日志)。如果结尾日志备份成功,则数据库管理员可以通过将数据库还原到故障点来避免任何工作丢失。

二. 文件备份策略  

   使用文件备份和日志备份还原数据库的操作可能比较复杂,因此最好先执行完整数据库备份,并在第一个文件备份开始之前,进行日志备份。下图在t0创建数据库后,立即执行完整数据库备份t1,创建第一个完整数据库备份后,便可以开始执行事务日志备份。事务日志备份按计划的间隔时间执行,文件备份以最适合数据库业务要求的间隔执行,下面是先备份主文件组A,再是辅助文件组B。在完整恢复模式下,恢复一个文件组备份,不但需要恢复文件组备份本身,还需要依次恢复从上一次完整数据库备份后到恢复的目标时间点为止的所有日志备份。如果日志备份数量多,可以考虑再给合差异文件备份,但这样备份计划更加难于管理。

图片 9

 

 系统数据库备份

  SQL Server
维护一组系统级数据库(称为“系统数据库”),这些数据库对于服务器实例的运行至关重要。
每次进行大量更新后,都必须备份多个系统数据库。
必须备份的系统数据库包括 msdb、 master和 model
如果有任何数据库在服务器实例上使用了复制,则还必须备份 distribution 系统数据库。
备份这些系统数据库,就可以在发生系统故障(例如硬盘丢失)时还原和恢复 SQL
Server 系统。

  而往往系统数据库得不到关注,在维护任务中是缺失的。

五. 备份演示

-- 给二个表插入数据
insert into Student values(1,'张三','广东深圳')
insert into Teacher values(1,'李四','广东佛山')

-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice

-- 给二个表插入数据
insert into Student values(2,'张三2','广东深圳')
insert into Teacher values(2,'李四2','广东佛山')

-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice

-- 文件组FG_Test_Id_01备份
backup database [FileGroupTest] file='FG_TestUnique_Id_01_data' to BackupTestDevice

-- 给二个表插入数据
insert into Student values(3,'张三3','广东深圳')
insert into Teacher values(3,'李四3','广东佛山')
-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice
-- 给二个表插入数据
insert into Student values(4,'张三4','广东深圳')
insert into Teacher values(4,'李四4','广东佛山')
-- 日志备份
backup log  [FileGroupTest] to BackupTestDevice

-- 文件组FG_Test_Id_02备份
backup database [FileGroupTest] file='FG_TestUnique_Id_02_data' to BackupTestDevice

-- 给主文件组表插入数据
insert into School values(1,'深圳大学','广东深圳南山')
-- 主文件组备份
backup database [FileGroupTest] file='FileGroupTest' to BackupTestDevice

  查看备份集如下图所示:type=F 代表文件组备份类型

图片 10

 验证备份可用性

  验证备份但不还原备份,检查备份集是否完整以及整个备份是否可读。
但是,RESTORE VERIFYONLY 不尝试验证备份卷中的数据结构。 在Microsoft SQL Server 中,RESTORE VERIFYONLY 得到了增强以对数据进行附加检查,从而提高检测到错误的可能性。
其目标是尽可能接近实际的还原操作。

  RESTORE VERIFYONLY 执行下列检查:

  • 备份集是否完整以及所有卷是否可读。

  • 数据库页中的一些标头字段,例如页 ID(就如同要写入数据一样)。

  • 校验和(如果介质中提供的话)。

  • 目标设备中是否有足够的空间。

 

六. 还原演示

--步骤1:假设文件FG_TestUnique_Id_01_data已损坏,数据库处于在线状态来还原该文件
restore database [FileGroupTest] file='FG_TestUnique_Id_01_data' 
from BackupTestDevice with file=33, norecovery 

  图片 11

--此时FileGroupTest库还能用,但FG_Test_Id_01文件组上的Student表现不能用,此时处于离线状态
select * from FileGroupTest.dbo.Student

  图片 12

--这两个表在不同文件组上,可以使用
select * from FileGroupTest.dbo.School
select * from FileGroupTest.dbo.Teacher

    图片 13
 BACKUP LOG 与 COPY_ONLY
选项将创建仅复制日志备份,该备份不会截断事务日志。
仅复制日志备份对日志链没有任何影响,因此其他日志备份的表现就像仅复制备份不存在一样。

--步骤2:进行新的日志备份,以确保捕获到该文件离线时的点
backup log  [FileGroupTest] to BackupTestDevice with copy_only

  图片 14

--步骤3: 在线还原日志备份
restore log [FileGroupTest] from BackupTestDevice with file=34,norecovery
restore log [FileGroupTest] from BackupTestDevice with file=35,norecovery
restore log [FileGroupTest] from BackupTestDevice with file=38,recovery

--离线的文件组FG_Test_Id_01处于在线状态,Student表可以使用,数据库恢复完成
select * from FileGroupTest.dbo.Student

  图片 15

 有异地备份

  防止本地磁盘损坏或者整个机房故障,对这种至关重要的数据,必须采取异地备份的办法。

 使用校验和(CHECKSUM)

  此选项主要是在备份的时候校验是否存在残缺页(也可以理解成是否有数据页损坏),开启此选项可以在备份时及时发现数据是否存在问题。

  图片 16

 

  详细说明请参见:数据库备份checksum选项你会用么?

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website