超大数据库的备份和恢复问题：分区表、文件组备份、部分还原

master

go

alter database wc

set single_user

with rollback immediate

drop database wc

go

--1.创建数据库

create database wc

on primary

(

name = wc_data,

filename = ‘D:\wc_data.mdf‘

)

log on

(

name = wc_log1,

filename = ‘d:\wc_log1.ldf‘

),

(

name = wc_log2,

filename = ‘d:\wc_log2.ldf‘

)

--2.增加文件组

alter database wc

add filegroup wc_fg1

alter database wc

add filegroup wc_fg2

alter database wc

add filegroup wc_fg3

alter database wc

add filegroup wc_fg4

--3.把文件添加到文件组中

alter database wc

add file

(

name = wc_fg1_1,

filename = ‘d:\wc_fg1_1.ndf‘,

size = 1MB

)

to filegroup wc_fg1

alter database wc

add file

(

name = wc_fg2_1,

filename = ‘d:\wc_fg2_1.ndf‘,

size = 1MB

)

to filegroup wc_fg2

alter database wc

add file

(

name = wc_fg3_1,

filename = ‘d:\wc_fg3_1.ndf‘,

size = 1MB

)

to filegroup wc_fg3

alter database wc

add file

(

name = wc_fg4_1,

filename = ‘d:\wc_fg4_1.ndf‘,

size = 1MB

)

to filegroup wc_fg4

go

use wc

go

--4.创建分区函数

create partition function wcLeftRange(datetime)

as range left for values(‘2006-01-01‘,‘2007-01-01‘,‘2008-01-01‘)

create partition function wcRightRange(datetime)

as range right for values(‘2006-01-01‘,‘2007-01-01‘,‘2008-01-01‘)

--5.创建分区方案

create partition scheme wcLeftRangeScheme

as partition wcLeftRange

to (wc_fg1,wc_fg2,wc_fg3,wc_fg4)

--6.创建分区表

create table dbo.wcT

(wcId bigint not null,

wcV varchar(100) not null ,

wcDate datetime not null,

constraint pk_wcid_date

primary key(wcId,wcDate)

)

on wcLeftRangeScheme(wcDate)

insert into dbo.wcT(wcId,wcV,wcDate)

values(1,‘2‘,‘2006-01-01 00:00:00‘),

(2,‘1‘,‘2005-12-31 23:59:59‘),

(3,‘2‘,‘2006-12-31 23:59:59‘),

(4,‘3‘,‘2007-01-01 00:00:00‘),

(5,‘4‘,‘2008-01-01 00:00:00‘),

(6,‘4‘,‘2008-12-31 23:59:59‘)

--7.显示每条数据所属分区号,从1开始计算

select *,

--$partition函数,后面是分区函数名称,列名称

$partition.wcLeftRange(wcDate) as partition

from wcT

create table T

(

id int primary key identity(1,1),

v varchar(10)

) on [primary]

insert into t

values(‘b‘)

接下来备份数据库：

--备份数据库
 
backup database wc
filegroup =‘primary‘
to disk = ‘d:\primary.bak‘
 
 
backup database wc
filegroup = ‘wc_fg1‘
to disk = ‘d:\wc_fg1.bak‘
 
backup database wc
filegroup = ‘wc_fg2‘
to disk = ‘d:\wc_fg2.bak‘
 
backup database wc
filegroup = ‘wc_fg3‘
to disk = ‘d:\wc_fg3.bak‘
 
backup database wc
filegroup = ‘wc_fg4‘
to disk = ‘d:\wc_fg4.bak‘
 
backup log wc
to disk =‘d:\wc_log.trn‘

 

下面，要模拟数据文件的损坏。

接下来，停止服务：net stop mssqlserver。

然后，把wc_fg1文件组所对应的文件：d:\wc_fg1_1.ndf 删掉。

重启服务：net  start  mssqlserver。

还原数据库，采用部分还原，使主文件组以及wc_fg1文件组，处于联机状态，这2个文件组的数据就可以访问：

/*
若要启动部分还原顺序，请使用 RESTORE 语句的 WITH PARTIAL 子句，
并提供一个备份集，其中至少包含主数据文件的完整副本。
不能出于任何其他目的使用 RESTORE 语句的 WITH PARTIAL 子句。
*/
restore database wc
filegroup =‘primary‘
from disk = ‘d:\primary.bak‘
with replace,
     partial,
     norecovery
 
 
restore database wc
filegroup = ‘wc_fg1‘
from disk = ‘d:\wc_fg1.bak‘
with norecovery
 
restore log wc
from disk =‘d:\wc_log.trn‘

 

查询T表、wcT表的数据：

--正常返回数据
select *
from wc.dbo.t
 
 
--如果只查询第一个分区的数据,可以正常返回数据,不会报错
select *,
 
       --$partition函数,后面是分区函数名称,列名称
       $partition.wcLeftRange(wcDate) as partition 
from wc.dbo.wcT
where $partition.wcLeftRange(wcDate) =1
 
 
--如果查询第1、2个分区的数据，会报错
select *,
 
       --$partition函数,后面是分区函数名称,列名称
       $partition.wcLeftRange(wcDate) as partition 
from wc.dbo.wcT
where $partition.wcLeftRange(wcDate) in (1,2)
 
 
--继续恢复第二个文件组的数据
restore database wc
filegroup = ‘wc_fg2‘
from disk = ‘d:\wc_fg2.bak‘
with norecovery
 
restore log wc
from disk =‘d:\wc_log.trn‘
with recovery
 
/*
--备份尾部日志
backup log wc
to disk = ‘d:\wc_1.trn‘
with norecovery
--还原尾部日志
restore log wc
from disk = ‘d:\wc_1.trn‘
with recovery
*/
 
--不再报错，返回第1、2个分区的数据
select *,
 
       --$partition函数,后面是分区函数名称,列名称
       $partition.wcLeftRange(wcDate) as partition 
from wc.dbo.wcT
where $partition.wcLeftRange(wcDate) in (1,2)

1.通过上面的实验，说明了分区表的高可用性。

也就是如果还原了主文件组和第一个辅助文件组，那么这个分区表在第一个辅助文件组的数据，也就是第一个分区的数据，就可以访问，接下来可以继续恢复其他文件组。

 

2.还有一个问题是，如果是误删了分区表中第3个分区的数据，那么要如何才能恢复呢？

要想简单的通过直接还原第3个文件组的备份文件，来恢复是不可能的。

这个其实也是和上面的一样，通过部分还原，还原主文件组和第三个文件组，再把数据导出来，再导入原来的数据库就可以了，这样速度快多了。

 

3.另外，要恢复，必须要有日志备份，也就是在备份文件组后，必须要备份事务日志。

之所以要备份事务日志是因为，当备份主文件组后，在备份辅助文件组时，可能主文件组的数据会变化，这时如果不备份日志文件，会导致还原主文件组后，再还原辅助文件组时，就会有不一致，也就是说主文件组和辅助文件组，不完全是同一个时间点备份的，导致LSN不一致，这时通过之前备份的日志文件来还原，就可以使得整个数据处于一致状态。

 

 

下面更进一步，把分区表、文件组备份、差异备份、日志备份，部分还原、基于事务日志还原到特定时间点，相结合。

运用上面相同的建库脚本后，进行文件组的备份，和文件组的差异备份，事务日志的备份：

--文件组的备份
backup database wc
filegroup =‘primary‘
to disk = ‘c:\primary.bak‘
 
backup database wc
filegroup = ‘wc_fg1‘
to disk = ‘c:\wc_fg1.bak‘
 
 
--第1次删除数据
delete from wc.dbo.wcT  
where wcid = 1
 
select GETDATE() --2013-09-07 14:16:36.910
 
 
 
--文件组的差异备份
backup database wc
filegroup =‘primary‘
to disk = ‘c:\primary_diff.bak‘
with differential
 
backup database wc
filegroup = ‘wc_fg1‘
to disk = ‘c:\wc_fg1_diff.bak‘
with differential
 
 
--第二次删除数据
delete from wc.dbo.T
delete from wc.dbo.wcT where wcId = 2
 
select GETDATE()  --2013-09-07 14:16:54.183
 
 
 
--日志备份
backup log wc
to disk = ‘c:\wc_log.trn‘
 
select GETDATE()  --2013-09-07 14:17:18.853

进行还原：

 
--1.基于文件组备份,日志备份,还原到指定时间点:恢复到第1次删除数据之前
restore database wc
filegroup =‘primary‘
from disk = ‘c:\primary.bak‘
with replace ,
     partial,
     norecovery
 
restore database wc
filegroup = ‘wc_fg1‘
from disk = ‘c:\wc_fg1.bak‘
with norecovery
 
 
restore log wc
from disk = ‘c:\wc_log.trn‘
with recovery,
     stopat = ‘2013-09-07 14:16:36‘
 
 
 
select * from wc.dbo.wcT
where $partition.wcLeftRange(wcDate) = 1
 
select * 
from wc.dbo.T
 
 
 
--2.基于文件组备份,日志备份,
--还原到指定时间点:恢复到第1次删除数据之后,第2次删除数据之前
restore database wc
filegroup =‘primary‘
from disk = ‘c:\primary.bak‘
with replace ,
     partial,
     norecovery
 
restore database wc
filegroup = ‘wc_fg1‘
from disk = ‘c:\wc_fg1.bak‘
with norecovery
 
restore log wc
from disk = ‘c:\wc_log.trn‘
with recovery,
     stopat = ‘2013-09-07 14:16:37‘
 
 
select * from wc.dbo.wcT
where $partition.wcLeftRange(wcDate) = 1
 
select * 
from wc.dbo.T
 
 
 
--3.基于文件组备份,差异备份,日志备份,
--还原到指定时间点:恢复到第2次删除数据之后
restore database wc
filegroup =‘primary‘
from disk = ‘c:\primary.bak‘
with replace ,
     partial,
     norecovery
 
restore database wc
filegroup = ‘wc_fg1‘
from disk = ‘c:\wc_fg1.bak‘
with norecovery
 
 
 
restore database wc
filegroup =‘primary‘
from disk = ‘c:\primary_diff.bak‘
with norecovery
 
restore database wc
filegroup = ‘wc_fg1‘
from disk = ‘c:\wc_fg1_diff.bak‘
with norecovery
 
 
restore log wc
from disk = ‘c:\wc_log.trn‘
with recovery,
     stopat = ‘2013-09-07 14:16:55‘
 
 
select * from wc.dbo.wcT
where $partition.wcLeftRange(wcDate) = 1
 
select * 
from wc.dbo.T
 

 

 

• 点赞 1
• 收藏
• 分享
• • 文章举报

不想长大啊 发布了416 篇原创文章 · 获赞 135 · 访问量 94万+ 他的留言板 关注

超大数据库的备份和恢复问题：分区表、文件组备份、部分还原

标签：values   active   list   discover   replace   more   watch   data-   原创文章