数据库备份那点事儿

一. 概述

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

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

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

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

写在前面

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

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

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

二. 文件备份策略  

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

图片 1

 

一些名词

  完整数据库备份:完整数据库备份就是复制数据库里的所有信息,通过单个完整备份,就能将数据库恢复到某个时间点的状态。

注:由于数据库备份是一个在线的操作,一个大的完整数据库备份可能需要一个小时甚至更长的时间,数据库在这段时间里还会发生变化,所以完整数据库备份还要对部分事务日志进行备份,以便能够恢复数据库到一个事务一致的状态。

  文件备份:文件备份指备份一个或多个文件或文件组中的所有数据。

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

使用文件备份能够只还原损坏的文件,而不用还原数据库的其余部分,从而可加快恢复速度。例如,如果数据库由位于不同磁盘上的若干个文件组成,在其中一个磁盘发生故障时,只需还原这个故障磁盘上的文件的备份,其他磁盘上的文件无须还原,这样会缩短还原时间。
  部分备份:部分备份与完整数据库备份类似,但是部分备份默认只包含数据库可读写部分,数据库的只读文件将不会被备份。

注:因为只读部分是不会发生变动的,总是去备份它有点浪费时间与精力所以部分备份在希望不备份只读文件组时非常有用。部分备份可以说是数据库备份和文件备份之间的一个中间类型。如果一个数据库里没有只读文件,那么部分备份和数据库备份就没什么差别。 

  差异备份:差异备份要求数据库之前做过一次完整备份。差异备份仅捕获自该次完整备份后发生更改的数据,这个完整备份被称为差异备份的“基准”。差异备份仅包括建立差异基准后更改的数据。差异备份比差异基准更小且更快,便于执行频繁备份,从而降低了数据丢失的风险。

  日志备份:数据备份集中精力于数据文件的备份。对于日志文件,相应地有事务日志备份。每个日志备份包括创建备份时处于活动状态的部分事务日志,以及先前日志备份中未备份的所有日志记录。不间断的日志备份序列包含数据库的完整(即连续不断的)日志链。在完整恢复模式下(或者在大容量日志恢复模式下的某些时候),连续不断的日志链可以将数据库还原到任意时间点。

  尾日志备份:“结尾日志备份”捕获尚未备份的任何日志记录(“结尾日志”),以防丢失所做的工作并确保日志链完好无损。
在将 SQL Server
数据库恢复到其最近一个时间点之前,必须先备份数据库的事务日志。
结尾日志备份将是数据库还原计划中相关的最后一个备份。

注意:并非所有还原方案都要求执行结尾日志备份。
如果恢复点包含在较早的日志备份中,则无需结尾日志备份。
此外,如果您准备移动或替换(覆盖)数据库,并且在最新备份后不需要将该数据库还原到某一时间点,则不需要结尾日志备份。

  仅复制备份(Copy-Only):独立于常规SQL Server备份序列的SQL
Server备份。通常,进行备份会更改数据库并影响其后备份的还原序列。但是,有时在不影响数据库全部备份和还原过程的情况下,为特殊目的而进行备份还是有用的。为实现此目的,SQL
Server引人了下列两种仅复制备份
  (1)仅复制完整备份
仅复制完整备份也备份整个数据库的内容。它和正常的完整备份的区别是,做完了以后差异备份的基准不会变,因此不影响差异备份序列。
  (2)仅复制日志备份
仅复制日志备份只备份当前日志文件里现有的内容,但是不会清空日志文件里备份下的日志。因此,下次再做正常日志备份的时候,这些内容还会被再次备份下来,从而不影响常规日志备份的序列。这种备份主要用在以下情况:数据库上已经有了一个备份计划任务在运行,但是现在需要紧急做一个日志备份,但同时不能影响到原有的备份序列。

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

恢复模式 说明 工作丢失的风险 能否恢复到时点?
Simple 无日志备份。

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

简单恢复模式不支持要求事务日志备份的操作。 在简单恢复模式中不能使用以下功能:

-日志传送

-AlwaysOn 或数据库镜像

-没有数据丢失的介质恢复

-时点还原

最新备份之后的更改不受保护。 在发生灾难时,这些更改必须重做。 只能恢复到备份的结尾。 有关详细信息,请参阅完整数据库还原(简单恢复模式)。 

有关简单恢复模式的更多深入说明,请参阅由 MSSQLTips! 人员提供的 SQL Server 简单恢复模式

Full 需要日志备份。

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

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

正常情况下没有。

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

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

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

大容量日志 需要日志备份。

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

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

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

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

否则不丢失任何工作。

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

三.文件还原  

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

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

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

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

  (4)
按顺序还原事务日志备份,从时间上最早备份的日志文件开始,到步骤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:\Data\FG_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:\Data\FG_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

 生产系统不要使用简单恢复模式

  建议说明:简单恢复模式并不适合生产系统。因为对生产系统而言,丢失最新的更改是无法接受的,我们建议使用完整恢复模式。

  基础小知识:在简单模式下,可以采用两种备份方式:全备份和差异备份。这两种备份消耗都会比较大,所以不是可以频繁备份的类型,所以在两次备份间隔的时间段内数据都存在丢失的风险。微软官方文档
:简单恢复模式下的备份.aspx)

  实际场景小故事:很多维护人员喜欢简单模式,因为简单模式自动回收日志空间以减少空间需求,实际上不再需要管理事务日志空间。但实际情况时因为明白这其中的奥妙原理么?并不是,甚至相反,我在很多的客户系统看到跑着上TB的数据,而数据库备份模式竟然是简单模式,只有每天的全备份,连差异备份都没有。

  我一般会问:“现在的备份模式可能会丢一天的数据,公司能接受么?”  

  维护人员:“那肯定不能接受呀!”

  我又问:“那为什么不采用更好的备份方式呢?”

  维护人员:“我也不太懂,不知道该怎么做,数据库跑这么久了,没那么容易坏吧?”

  

 

五. 备份演示

-- 给二个表插入数据
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 代表文件组备份类型

图片 4

 使用完整恢复模式,要有日志备份计划

  建议说明:完整恢复模式使用日志备份在最大范围内防止出现故障时丢失数据,这种模式需要备份和还原事务日志(“日志备份”)。使用日志备份的优点是允许您将数据库还原到日志备份中包含的任何时点(“时点恢复”)。可以使用一系列日志备份将数据库前滚到其中一个日志备份中包含的任意时点。

  基础小知识:完整模式下,可以采用日志的频繁备份来缩小数据丢失的时间,比如:00:00点做了全备份,每10分钟一次日志备份,那么当23:50数据库损坏,只需要使用0点的全备份和损坏之前日志备份就可以还原到23:50的数据,而不是丢失整个近一天的数据。日志备份会使不活动的日志重用,这样也解决了完整模式下日志不断增长的问题。微软官方文档
:在完整恢复模式下备份.aspx)

  实际场景小故事:很多客户的系统采用了完整恢复模式,但是缺少日志备份,那么这样和简单模式有什么区别呢?有区别,没有降低数据丢失的风险反而增加了日志的空间消耗。很多时候被问到这样的问题,数据库日志很大,怎么收缩?很多数据库新手可能完全不知道日志备份的作用,而采用把恢复模式改成简单,然后收缩!再改回完整模式!比较搞笑的问题还有数据库搭建了镜像或AlwaysOn可用组(必须完整恢复模式),竟然把镜像拆掉,然后改成简单,收缩后

再重新搭建….很多时候只需要一个日志备份就可以解决的问题!

 

六. 还原演示

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

  图片 5

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

  图片 6

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

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

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

  图片 8

--步骤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

  图片 9

 系统数据库备份

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

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

 使用压缩备份

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

  图片 10

 

Copyright @ 2015-2020 ca88 版权所有
网站地图xml地图