浅谈SQL SERVER的备份还原模式

首先明确文件复制不等同于备份

在SQL SERVER中使用先写事务日志来实现事务持续性，每个事务都先写入事务日志，然后才写入数据文件，好处在于

1 事务日志确保能够将每个事务恢复到服务器停机前的状态

2事务日志使得能够在处理事务时进行备份

3 事务日志降低了硬件故障的影响，出于安全考虑最好把事务日志和数据文件放在不同的分区

SQL SERVER的三种恢复模式

数据库的还原模式

完全 对日志备份，可以还原到故障点

简单 不对日志备份，只能还原到备份点

大容量日志：折中的方案，如果没有大容量操作是可以恢复到故障点的，如果有大容量操作，则所有的大容量操作丢失

备份方法

1完全备份 （包含所有的数据信息）

2差异备份（备份至完全备份以来数据的差异量）

3日志备份（备份的日志的内容）

恢复计划

如果是数据库还原模型是完整模式则

先备份故障点的日志

再还原最新完全备份

再还原完全备份后的最新差异备份

再还原差异备份后的所有日志备份

如果数据库还原模式是简单模式

则只需要还原最新的完全备份

然后还原完全备份后的最新的一次差异备份

上图为简单恢复模式，由于没有日志备份，所以当在t5时刻发生故障，只能将数据恢复到T5

上图为完整恢复模式，由于有日志备份，所以当故障发生，先在故障点做一次日志备份，再还原Db_1数据库，再还原log_1,log_2，故障点log，就可以成功恢复数据

数据库恢复模式决定了你的备份类型

通常备份策略是多种备份方法一起使用

完整备份和还原

只能选择任意的一个完整数据库备份进行还原，还原到10,11,12

完整备份+差异备份与还原

如果需要还原到11时的数据库状态

先还原备份1，再还原备份2

如果需要还原到12:30时的数据库状态

方法1 先还原备份2，再还原备份3

方法2 先还原备份1，再还原备份3

完整备份+日志备份与还原

如果需要还原到11时的数据库状态

完整备份1+日志备份1+日志备份2

如果需要还原到12:30时的数据库状态

方法1完整备份2+日志备份3

方法2 完整备份1+日志备份1+日志备份2+日志备份3

如果需要恢复到10:45时的状态

完整备份1+日志备份1+日志备份2 再指定到10:45的恢复即时点

常用备份和恢复语句

完全备份

backup database xscj to mybak

备份到一个文件里

backup database xscj to disk='e:\a.bak'

差异备份

要保证一定已经做过了一次完全备份

backup database xscj to mybak with differential ,nopoint

日志备份

backup log xscj to mybak with noinit

注意：还原模式为简单时，不允许做日志备份

故障点备份

实际就是日志备份，备份的是你上一次日志备份后到你的故障点之间的日志

backup log xscj to myback with no_truncate

如何去截断日志，节省磁盘空间

backup log xscj with truncate_only

从备份中恢复

恢复数据备份

restore database xscj from mybak

恢复日志备份

restore log xscj from mybak

从一个备份文件中恢复

restore database xscj from disk='c:\xs_full.bak'

如果备份集中有多个备份

restore database xscj from mybak with file=1

--recovery 还原到数据库为可以使用状态，以后再还原任何日志

--norecovery 数据库没有被还原到可用状态，要等待其他的还原

实践

--1创建一个数据库，设置数据库还原模式为完整模式

--2创建一张表，做一次完全备份

use test

go

create table xs( name char(10),age tinyint)

go

--创建备份设备

exec sp_addumpdevice 'disk','xs_bak','c:\xs_bak.bak'

backup database test to xs_bak

--3插入两条数据库，做一次差异备份

select * from xs

insert into xs values('tom',1)

insert into xs values('mary',2)

backup database test to xs_bak with differential

--4更新数据库做一次日志备份

update xs

set age =10

where name='tom'

go

select * from xs

go

backup log test to xs_bak

--5插入数据库，不做任何备份

insert into xs values('bob',3）

--6损坏数据库

--停止服务，删除数据库test的数据文件

--注意不要删除日子文件，否则就无法恢复到故障点

--7要求还原到最后插入数据的状态（也就是故障点）

--a 做故障点备份

backup log test to xs_bak with no_truncate

--b还原最近的一次完全备份

restore database test from xs_bak with file=1,norecovery

--c还原完全备份后的最近一次差异备份

restore database test from xs_bak with file=2,norecovery

--d还原差异备份后的所有日子备份

restore log test from xs_bak with file=3,norecovery

restore log test from xs_bak with file=4,recovery

检测

use test

go

select * from xs

本文转自 rebelxuan 51CTO博客，原文链接:http://blog.51cto.com/nizhuan/736883