兴發国际娱乐手机登录 12

SqlServer备份恢复,开始数据库备份

一.概述

  前面讲了备份的一些理论知识,这篇开始讲在简单恢复模式下的备份与还原。在简单模式下是不能做日志备份的,发生灾难后,数据库最后一次备份之后做的数据修改将是全部丢失的,所以在生产环境下,数据又很重要,一般不建议使用这种模式。
例如对一个数据库有5次完整数据备份,时间是t5,  之后发生灾难,就会部丢失。

兴發国际娱乐手机登录 1

  当数据库越来越大,完整备份时间会越来越长,为了减少丢失风险,引入差异备份。例如下图演示:在第一次建立数据库完整备份后,建立了三次差异备份,之后再建立完整备份,从而建立新的差异基准。不管是完整备份还是差异备份,一般只能在晚间进行。如果数据比较庞大又不允许长时间数据丢失,那简单恢复模式是不能满足的。

兴發国际娱乐手机登录 2

 

备份 (Transact SQL) | Microsoft Docs
   

数据库备份分为数据文件备份和日志文件备份,数据文件的备份分为:完整备份和差异备份。在SQL
Server
2012中,能够将数据分布式备份到不同的存储设备上,一般情况,只将数据备份到一个备份文件(.bak)中,只有在备份超大的数据库时,才需要分布式备份,对于备份集(backup
set),备份介质(backup Media),备份族(backup
family),镜像备份,等等看似复杂的术语,不用深入了解,简单了解一下基本知识:

二.备份演示

  在简单恢复模式下主要的备份是完整备份和差异备份。我这里有TestLog库,库里有二个表。假设周日做一次完整备份,周一到周六晚上每天做一次差异备份,到第二周的周日时开始新的基准线。如下所示

use test
exec sp_addumpdevice 'disk', 'BackupTestDevice','F:\SqlService\backup\BackupTestBackup.bak'

  兴發国际娱乐手机登录 3

--设置恢复模式为简单恢复
 ALTER DATABASE TestLog SET RECOVERY simple
go
-- 做一次完整备份到备份设备中(备份基准) 假设在周日晚上
backup database  TestLog to BackupTestDevice
go

兴發国际娱乐手机登录 4

go
--差异备份 周一晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周二晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周三晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周四晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周五晚
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周六晚
backup database TestLog to BackupTestDevice with differential 
go
--完整备份 周日晚(新基准)
backup database TestLog to BackupTestDevice with differential 
go
--差异备份 周一晚
backup database TestLog to BackupTestDevice with differential 

 在备份设备中查看备份集
  兴發国际娱乐手机登录 5

-- 通过脚本查看

select distinct
s.first_lsn,s.last_lsn,s.database_backup_lsn,s.position,
s.backup_finish_date,s.type,y.physical_device_name,s.backup_size
from msdb..backupset as s inner join
msdb..backupfile as f on f.backup_set_id=s.backup_set_id inner
join
msdb..backupmediaset as m on s.media_set_id=m.media_set_id inner
join
msdb..backupmediafamily as y on m.media_兴發国际娱乐手机登录,set_id=y.media_set_id
where s.database_name=’TestLog’
order by s.position asc

兴發国际娱乐手机登录 6

 

 

  • backup
    set:是数据或日志的一次备份;
  • backup
    media:是备份存储的文件,分为两部分:media
    header和content,content是由一个或多个backup sets构成的;
  • 镜像备份:一次备份操作创建一个相同的备份,最多三个镜像备份;
  • backup
     family:多个备份设备和镜像备份构成backup family;

三. 还原演示

   将一个数据库还原,需要构造一个正确的还原顺序。在还原过程中,备份文件结尾使用norecovery事务不恢复(正在还原。。)不可读写,在最后一个备份文件结尾使用recovery事务恢复。数据库恢复正常。

-- 切换到master库
use master

--设置单用户模式(否则执行下面报错:“因为数据库正在使用,所以无法获得对数据库的独占访问权”)
ALTER DATABASE TestLog SET OFFLINE WITH ROLLBACK IMMEDIATE

-- 从备份恢复一个全备份 ,norecovery(正在还原...)不可读写. file指备份集位置号
restore database TestLog from BackupTestDevice with file=1, norecovery 

  兴發国际娱乐手机登录 7兴發国际娱乐手机登录 8

-- 恢复到差异备份文件3,跳过差异备份2 ,看是否备份成功
restore database TestLog from BackupTestDevice  with file=3, recovery

  兴發国际娱乐手机登录 9兴發国际娱乐手机登录 10

-- 备份结束之后,结束单用户模式
ALTER  database  TestLog  set   online  

 下面在来演示还原差异文件,使用旧基准。还原看会怎么样

-- 从旧基准中恢复一个全备份 ,norecovery(正在还原...)不可读写. file是1
restore database TestLog from BackupTestDevice with file=1, norecovery 

--新基准file是8, 恢复到差异备份文件9 
restore database TestLog from BackupTestDevice  with file=9, recovery

兴發国际娱乐手机登录 11

 

 总结:对于简单恢复模式,没有日志备份,恢复只需要一个完整数据库备份,以及最后一个差异备份。
对于多个差异备份文件,在还原时不需要LSN的连续性(在同一个基准内)。

 

还原 (Transact SQL) | Microsoft Docs
  

数据库备份的策略一般是:一周一次完整备份,一天一次差异备份,一小时一次事务日志备份,根据数据容灾的要求,适当增减备份的时间间隔。

 

为了便于管理数据备份文件,推荐的做法是:

恢复模式

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

 

  • 数据/日志的每次备份都使用一个单独的备份文件,数据备份的扩展名是
    .bak,日志备份的扩展名是.trn;
  • 合理命名每个备份文件,建议使用:database_name+date+time+(.bak/.trn),该命名方式,很容易识别备份的数据库和开始备份的时间;
  • 创建schedule,定时清理备份文件,避免备份文件耗尽磁盘空间;

备份

一,创建数据库的完整备份和差异备份

完整备份

 完整数据库备份可对整个数据库进行备份。 这包括对部分事务日志进行备份,以便在还原完整数据库备份之后,能够恢复完整数据库备份。 完整数据库备份表示备份完成时的数据库。

使用backup
database命令创建数据库的数据文件的备份,backup database
命令语法(简化):

例如 ( Transact-SQL)

下面的示例说明了如何使用 WITH FORMAT
覆盖任意现有备份并创建新介质集,从而创建一个完整数据库备份。 然后,此示例将备份事务日志。 在现实情况下,您必须执行一系列的定期日志备份。 在此示例中, AdventureWorks2012 示例数据库设置为使用完整恢复模式。

USE master;
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO
— Back up the AdventureWorks2012 database to new media set (backup set
1).

BACKUP DATABASE AdventureWorks2012
TO DISK = ‘Z:\SQLServerBackups\AdventureWorks2012FullRM.bak’
WITH FORMAT;
GO
–Create a routine log backup (backup set 2).
BACKUP LOG AdventureWorks2012 TO DISK =
‘Z:\SQLServerBackups\AdventureWorks2012FullRM.bak’;

GO

* *

BACKUP DATABASE database_name 
TO DISK  =  'physical_device_name'
[ WITH { DIFFERENTIAL
|  COPY_ONLY 
| { COMPRESSION | NO_COMPRESSION } 
| { NOINIT | INIT } 
| { NOSKIP | SKIP } 
| { NOFORMAT | FORMAT } 
| STATS [ = percentage ] }]

差异备份

差异备份所基于的是最近一次的完整数据备份。 差异备份仅捕获自该次完整备份后发生更改的数据。 差异备份所基于的完整备份称为差异的“基准”
。 完整备份(仅复制备份除外)可以用作一系列差异备份的基准,包括数据库备份、部分备份和文件备份。 文件差异备份的基准备份可以包含在完整备份、文件备份或部分备份中。

优点

  • 与创建完整备份相比,创建差异备份的速度可能非常快。 差异备份只记录自差异备份所基于的完整备份后更改的数据。 这有助于频繁地进行数据备份,减少数据丢失的风险。 但是,在还原差异备份之前,必须先还原其基准。 因此,从差异备份进行还原必然要比从完整备份进行还原需要更多的步骤和时间,因为这需要两个备份文件。

  • 如果数据库的某个子集比该数据库的其余部分修改得更为频繁,则差异数据库备份特别有用。 在这些情况下,使用差异数据库备份,您可以频繁执行备份,并且不会产生完整数据库备份的开销。

  • 在完整恢复模式下,使用差异备份可以减少必须还原的日志备份的数量。

 

 

 

1,完整备份和差异备份

日志备份

 

 

 

差异备份由DIFFERENTIAL 关键字指定,只备份从上一次完整备份之后发生更新的数据,而不是备份整个数据库,通常情况下,差异备份比完整备份占用的空间更少。差异备份的参考基准是上一次完整备份,而,事务日志,只备份是从上一次差异备份之后产生的事务日志。因此,备份是有顺序的,如果存在以下备份序列:

仅复制备份

 仅复制备份
是独立于常规 SQL Server 备份序列的 SQL Server 备份。 通常,进行备份会更改数据库并影响其后备份的还原方式。 但是,有时在不影响数据库总体备份和还原过程的情况下,为特殊目的而进行备份还是有用的。 仅复制备份就是用于此目的。

 

 

  1. FullBackup1.bak
  2. DifferentialBackup2.bak
  3. LogBackup3.trn
  4. DifferentialBackup4.bak
  5. LogBackup5.trn
  6. 出现错误

 

还原的策略是:备份尾日志,使数据库处于Restoring状态,依次还原FullBackup1.bak,DifferentialBackup4.bak,LogBackup5.trn,尾日志,就能将数据库还原到一个合适的有效时间点。

恢复

在执行完整备份和差异备份时,SQL
Server会备份足够的事务日志,用于将数据库还原到一致性的状态。对于master数据库,只能执行完整备份。

完整数据库还原(简单恢复模式) 

 

数据库完整还原的目的是还原整个数据库。 整个数据库在还原期间处于脱机状态。 在数据库的任何部分变为联机之前,必须将所有数据恢复到同一点,即数据库的所有部分都处于同一时间点并且不存在未提交的事务。

在简单恢复模式下,数据库不能还原到特定备份中的特定时间点。

用于还原完整数据库备份的基本 Transact-SQLRESTORE 语法是:

RESTORE
DATABASE database_name FROM backup_device [ WITH NORECOVERY
]

示例
(Transact-SQL)

以下示例首先显示如何使用 BACKUP 语句来创建 AdventureWorks2012 数据库的完整数据库备份和差异数据库备份。 然后按顺序还原这些备份。 将数据库还原到完成差异数据库备份时的状态。

该示例说明数据库完整还原方案的还原序列中的关键选项。 还原顺序 由通过一个或多个还原阶段来移动数据的一个或多个还原操作组成。 将省略与此目的不相关的语法和详细信息。 在恢复数据库时,尽管 RECOVERY
选项是默认值,但为清楚起见,仍建议显式指定该选项。

USE master;
–Make sure the database is using the simple recovery model.
ALTER DATABASE AdventureWorks2012 SET RECOVERY SIMPLE;
GO
— Back up the full AdventureWorks2012 database.
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak’
WITH FORMAT;
GO
–Create a differential database backup.
BACKUP DATABASE AdventureWorks2012
TO DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak’
WITH DIFFERENTIAL;
GO
–Restore the full database backup (from backup set 1).
RESTORE DATABASE AdventureWorks2012
FROM DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak’
WITH FILE=1, NORECOVERY;
–Restore the differential backup (from backup set 2).
RESTORE DATABASE AdventureWorks2012
FROM DISK = ‘Z:\SQLServerBackups\AdventureWorks2012.bak’
WITH FILE=2, RECOVERY;
GO

 

 

 

 

 

  • During a full or differential
    database backup, SQL Server backs up enough of the transaction log
    to produce a consistent database when the backup is restored.

  • Only a full database backup can be
    performed on the master database.

完整数据库还原(完整恢复模式)

将数据库还原到故障点

 

通常,将数据库恢复到故障点分为下列基本步骤:

  1. 备份活动事务日志(称为日志尾部)。 此操作将创建结尾日志备份。 如果活动事务日志不可用,则该日志部分的所有事务都将丢失。

    重要

    在大容量日志恢复模式下,备份任何包含大容量日志操作的日志都需要访问数据库中的所有数据文件。 如果无法访问该数据文件,则不能备份事务日志。 在这种情况下,您必须手动重做自最近备份日志以来所做的所有更改。

    有关详细信息,请参阅结尾日志备份 (SQL
    Server)。

  2. 还原最新完整数据库备份而不恢复数据库
    (RESTORE DATABASE database_name FROM backup_device WITH
    NORECOVERY)。

  3. 如果存在差异备份,则还原最新的差异备份而不恢复数据库
    (RESTORE
    DATABASE database_name FROM differential_backup_device WITH
    NORECOVERY).。

    还原最新差异备份可减少必须还原的日志备份数。

  4. 从还原备份后创建的第一个事务日志备份开始,使用
    NORECOVERY 依次还原日志。

  5. 恢复数据库
    (RESTORE DATABASE database_name WITH RECOVERY)。 此步骤也可以与还原上一次日志备份结合使用。

    下图说明此还原顺序。 故障发生后 (1),将创建结尾日志备份
    (2)。 接着,将数据库还原到该故障点。 这涉及到还原数据库备份、后续差异备份以及在差异备份后执行的每个日志备份,包括结尾日志备份。

    兴發国际娱乐手机登录 12

2,只复制(COPY_ONLY )备份

基本 TRANSACT-SQL RESTORE 语法

上图中还原顺序的基本 RESTORE Transact-SQL 语法如下:

  1. RESTORE DATABASE database FROM full
    database backup
     WITH NORECOVERY;

  2. RESTORE
    DATABASE database FROM full_differential_backup WITH
    NORECOVERY;

  3. RESTORE
    LOG database FROM log_backup WITH NORECOVERY;

    对于其他每个日志备份,重复此还原日志步骤。

  4. RESTORE DATABASE database WITH
    RECOVERY;

 

 

示例:恢复到故障点 (Transact-SQL)

以下 Transact-SQL 示例显示了将数据库还原到故障点的还原顺序中的基本选项。 此示例将创建数据库的结尾日志备份。 接下来,此示例将还原完整数据库备份和日志备份,然后还原结尾日志备份。 此示例将在最后的单独步骤中恢复数据库。

USE master;
–Create tail-log backup.
BACKUP LOG AdventureWorks2012
TO DISK = ‘Z:\SQLServerBackups\AdventureWorksFullRM.bak’
WITH NORECOVERY;
GO
–Restore the full database backup (from backup set 1).
RESTORE DATABASE AdventureWorks2012
FROM DISK = ‘Z:\SQLServerBackups\AdventureWorksFullRM.bak’
WITH FILE=1,
NORECOVERY;

–Restore the regular log backup (from backup set 2).
RESTORE LOG AdventureWorks2012
FROM DISK = ‘Z:\SQLServerBackups\AdventureWorksFullRM.bak’
WITH FILE=2,
NORECOVERY;

–Restore the tail-log backup (from backup set 3).
RESTORE LOG AdventureWorks2012
FROM DISK = ‘Z:\SQLServerBackups\AdventureWorksFullRM.bak’
WITH FILE=3,
NORECOVERY;
GO
–recover the database:
RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
GO

 

 

备份是有顺序的,使用COPY_ONLY选项不会影响备份的正常顺序,仅仅创建一个数据库的副本。

将数据库还原到指定时间点

 

在完整恢复模式下,完整的数据库还原通常可恢复到日志备份中的某个时间点、标记的事务或
LSN。 但是,在大容量日志恢复模式下,如果日志备份包含大容量更改,则不能进行时点恢复。

时点还原方案示例

下例假定针对一个关键任务型数据库系统,每天午夜创建一个完整数据库备份;从星期一到星期六,每小时创建一个差异数据库备份;全天每
10 分钟创建一个事务日志备份。 若要将数据库还原到星期三凌晨 5:19 的状态, 请执行以下操作:

  1. 还原星期二午夜创建的完整数据库备份。

  2. 还原星期四凌晨 5:00
    创建的差异数据库 备份。

  3. 应用星期四凌晨
    5:10创建的事务日志 备份。

  4. 应用星期三凌晨 5:20
    创建的事务日志 备份,指定恢复进程仅应用到凌晨 5:19
    之前发生的事务。

    或者,如果需要将数据库还原到它在星期四凌晨
    3:04 的状态, 而在星期四凌晨 3:00
    创建的差异数据库备份已不可用, 则执行下列操作:

  5. 还原在星期三午夜创建的数据库备份。

  6. 还原星期四凌晨 2:00
    创建的差异数据库 备份。

  7. 应用从星期四凌晨 2:10 到 3:00
    创建的所有事务 日志 备份。

  8. 应用星期四凌晨 3:10
    创建的事务日志 备份,停止凌晨 3:04
    的恢复进程。

 

差异备份的基准是上一次完整备份,即差异是指从上一次full
backup之后,对数据文件执行的更新操作。如果执行一次Copy-Only的完整数据库备份,不会影响差异备份的base(基准),该base是上一次full
backup,而非本次 Copy-only full backup。

3,压缩数据{ COMPRESSION | NO_COMPRESSION }

在备份时,将数据压缩,由于压缩的备份较小,能够减少Disk Sapce和Disk
IO消耗,提高数据备份的速度,但是,备份文件的压缩和解压缩十分消耗CPU资源。

发表评论

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