SQL Server 大数据管理——数据归档(主文件备份)

SQL Server 大数据管理——数据归档


背景:

        数据库几百GB,甚至TB级别数据库,历史数据偶尔会用到,不能直接删除,就需要定期归档历史数据。以往在归档历史数据方案:

方案1:做全备保存归档数据,删除线上库归档数据

方案2:创建一个新库,将归档数据导入到新库,备份保留新库,删除线上库归档数据

        对于方案1,这样会有较多的不需要或者不能归档的数据重复备份,占用大量空间,并且一旦归档数据需要使用,拷贝、还原需要时间较长,效率低;同时对还原需要空间也较难以匹配;方案2解决了数据重复备份占用空间的问题,但数据转移需要时间较长

因此,采用本文叙述的方案,表分区+数据库主文件备份的备份策略,可以同时很好的解决方案1、2的问题。


一.    主文件数据移动到辅文件


因为在进行文件/组还原时,还需要用到主文件,所以在每次数据归档时,同时要对主文件备份归档,所以主文件要尽可能的小。需要尽可能的将数据移动到辅助文件上


1.1  新表处理:

修改数据库默认文件组,或者创建表时指定文件组,使得新创建的表均在辅助文件组上


--创建测试数据库

CREATE DATABASE [test]

CONTAINMENT = NONE

ON  PRIMARY

( NAME = N'test', FILENAME = N'D:\DB\test.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

LOG ON

( NAME = N'test_log', FILENAME = N'D:\DB\test_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

--增加文件组和辅助文件

alter database test add filegroup data

alter database test add file

(name=data,filename='D:\DB\data.ndf', SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [data]

alter database test add file

(name=data1,filename='D:\DB\data1.ndf',size=8192KB,filegrowth=65536KB) to filegroup data

修改默认文件组脚本

USE [test]

GO

IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'data')

ALTER DATABASE [test] MODIFY FILEGROUP [data] DEFAULT

GO


create table testdd(id int)

select OBJECT_NAME(object_id) tableName,i.name indexName,s.name spaceName

from sys.indexes i

left join sys.data_spaces s on i.data_space_id=s.data_space_id

where object_id =OBJECT_ID('dbo.testdd','U')

通过系统视图发现,创建的testdd表是在默认的data文件组下:


1.2  已经存在的表


1.2.1 不需要归档的表

不需要归档的表,可以通过创建、或删除聚集索引将数据移动到辅助文件上

alter database test add filegroup tableMetaData

alter database test add file

(name=tableMetaData,filename='D:\DB\tableMetaData.ndf', SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP tableMetaData

alter database test add filegroup dataMD

alter database test add file(

      name= dataMD,filename='D:\DB\dataMD.ndf',size=8192KB,filegrowth=65536KB

) to filegroup dataMD

create table testp(id int) on [primary]

create table testd(id int) on data

create table testMD(id int) on dataMD

create table testm(id int) on tableMetaData

--testp中插入100万行数据

DECLARE @max AS INT, @rc AS INT;

SET @max = 1000000;

SET @rc = 1;

INSERT INTO testp VALUES(1);

WHILE @rc * 2 <= @max

BEGIN

    INSERT INTO dbo.testp SELECT id + @rc FROM dbo.testp;

    SET @rc = @rc * 2;

END

INSERT INTO dbo.testp

SELECT id + @rc FROM dbo.testp WHERE id + @rc <= @max;

go

--testm中插入100万行数据

DECLARE @max AS INT, @rc AS INT;

SET @max = 100000;

SET @rc = 1;

INSERT INTO testm VALUES(1);

WHILE @rc * 2 <= @max

BEGIN

    INSERT INTO dbo.testm SELECT id + @rc FROM dbo.testm;

    SET @rc = @rc * 2;

END

INSERT INTO dbo.testm

SELECT id + @rc FROM dbo.testm WHERE id + @rc <= @max;

go


为testp表在文件组data上创建聚集索引

创建索引之前文件数据量

主文件和辅文件tableMetaData已经增长至9216,其他文件任然初始大小

1.2.1.1        创建聚集索引移动数据

CREATE CLUSTERED INDEX [CIx_testp] ON [dbo].[testp]

(

      [id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [data]

GO

从下图可以看到此时testp的数据已经移到data文件组下

通过下图可以看到,data文件大小增加一次,但主文件大小并没有减少

1.2.1.2        删除聚集索引移动数据

CREATE CLUSTERED INDEX [CIx_testm] ON [dbo].[testm]

(

      [id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON', ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

ON [data]

GO

--  删除聚集索引,并指数据定存储的文件组

DROP INDEX [CIx_testm] ON dbo.testm WITH (MOVE TO [dataMD], ONLINE=ON)

GO

从下图可以看到,testm表数据已经从tableMetaData文件组移动到dataMD文件组

同时从下图可以看到,通过删除聚集索引转移数据,原文件大小仍然没有改变(tableMetaData仍然是9216KB)


1.3  需要定期归档表数据的转移


根据归档规则,一般按数据产生的时间进行分区,创建表分区,一个分区对应一个文件组的一个文件

create table testpp(id int,date datetime) on [primary]

DECLARE @max AS INT, @rc AS INT;

SET @max = 1000000;

SET @rc = 1;

INSERT INTO testpp VALUES(1,'2018-01-01');

WHILE @rc * 2 <= @max

BEGIN

INSERT INTO dbo.testpp SELECT id + @rc,DATEADD(MI,@rc,[date]) FROM dbo.testpp;

SET @rc = @rc * 2;

END

INSERT INTO dbo.testpp

SELECT id + @rc,DATEADD(MI,@rc,[date]) FROM dbo.testpp WHERE id + @rc <= @max;

go

此时主文件大小仍然为9216KB没有增长

系统视图更新迟缓,导致文件大小和真实数据大小不一致

下面对testpp表进行分区

--添加文件组、文件

use test

alter database test

add filegroup Before2019

alter database test

add filegroup After2019

alter database test

add file(name=N'Before2019',filename='D:\DB\testPartion\Before2019.ndf'

,size=1mb, filegrowth=1mb)

to filegroup Before2019

alter database test

add file(name=N'After2019',filename='D:\DB\testPartion\After2019.ndf'

,size=1mb,  filegrowth=1mb)

to filegroup After2019

--创建分区函数

create partition function RangeTime(datetime)

as range left for values('2019-01-01')

--创建分区方案

create partition scheme RangeSchema_CreateTime

as partition [RangeTime]

to(Before2019,After2019)

GO

--为testpp表添加分区

USE [test]

GO

BEGIN TRANSACTION

CREATE CLUSTERED INDEX [ClusteredIndex_on_RangeSchema_CreateTime_636570819394124711] ON [dbo].[testpp]

(

[date]

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [RangeSchema_CreateTime]([date])

DROP INDEX [ClusteredIndex_on_RangeSchema_CreateTime_636570819394124711] ON [dbo].[testpp]

COMMIT TRANSACTION


1.4  收缩主文件


不管是创建聚集索引转移数据,删除聚集索引转移数据,或者创建表分区转移数据,源文件的空间都不会自动释放,需要对源文件进行收缩

USE [test]

GO

DBCC SHRINKFILE(N'test', 0) withno_infomsgs

--参数说明

--emptyfile 转移数据到同文件组的其他文件

--notruncate 移动数据页到文件前段,但不释放空间

--truncateonly 不移动数据页,释放尾部空间

--with no_infomsgs 取消显示所有信息消息

GO


二.    归档历史数据

2.1  将归档分区数据转为普通表数据

--在归档数据所在的分区文件组上创建和归档表结构相同的表

create table testpp2018(id int,date datetime) on Before2019

--查询partition_number 及其对应数据所在的文件组

;with cte as

(select

object_id

,OBJECT_NAME(i.object_id) tableName

,i.index_id

,dds.partition_scheme_id

,dds.destination_id as partition_number

,fg.groupid

,fg.groupname

,f.fileid

,f.name

,f.filename

--,p.partition_id

--,p.rows

from sys.destination_data_spaces dds,sys.indexes i,sys.sysfilegroups fg,sys.sysfiles f

where dds.partition_scheme_id=i.data_space_id

and dds.data_space_id=fg.groupid

and fg.groupid=f.groupid

)

,cte1 as(

select

ps.data_space_id as partition_scheme_id

,ps.name partiton_schemes_name

,pf.name partition_function_name

,pf.function_id

--,prv.value AS BoundaryValue

from sys.partition_schemes ps ,sys.partition_functions pf--,sys.partition_range_values prv

where ps.function_id=pf.function_id

--and pf.function_id=prv.function_id

)

select cte.tableName,cte.groupname,cte.name,cte.filename

,cte.partition_number,cte1.partiton_schemes_name,cte1.partition_function_name,p.rows

,prv.boundary_id,prv.value BoundaryValue

from cte

inner join cte1 on cte.partition_scheme_id=cte1 .partition_scheme_id

left join sys.partition_range_values prv on cte1.function_id=prv.function_id and cte.partition_number=prv.boundary_id

left join sys.partitions  p on cte.object_id=p.object_id and cte.index_id=p.index_id and cte.partition_number=p.partition_number

where  cte.object_id=OBJECT_ID('dbo.testpp','U')




--要移出2019年以前的数据,即partition_number=1 的分区,从分区表中移除归档数据到普通表

alter table testpp switch partition 1 to testpp2018

从上图可以看到分区1的记录数已经为0,下面将把分区1合并掉,并移除对应的文件和文件组

use test

go

--合并分区1

alter partition function RangeTime()

merge range('2019-01-01 0:00:00')

--移除归档分区的文件和文件组

alter database test remove file [Before2019]

alter database test remove filegroup [Before2019]

通过创建聚集索引的方式将表数据从分区文件移动到主文件

CREATE CLUSTERED INDEX [ClusteredIndex-20180321-145814] ON [dbo].[testpp2018]

(

[id] ASC

) ON [PRIMARY]

GO

DROP INDEX [ClusteredIndex-20180321-145814] ON [dbo].[testpp2018] WITH ( ONLINE = OFF )

GO

2.2  备份主文件

BACKUPDATABASE [test] FILEGROUP=N'PRIMARY'

TO  DISK=N'E:\backup\test_PRIMARY_2018.bak'

WITHNOFORMAT,INIT,  NAME =N'test-完整 数据库 备份',SKIP,NOREWIND,NOUNLOAD,  STATS= 10

GO


2.3  数据恢复测试


RESTOREDATABASE [test1] FILE=N'test'

FROM  DISK=N'E:\backup\test_PRIMARY.bak'

WITH  FILE= 1,

MOVEN'test'TON'D:\DB\test1\test1.mdf', 

MOVEN'test_log'TON'D:\DB\test1\test1_1.ldf', 

RECOVERY,  replace,  STATS= 10

GO


2.4  归档说明


备份的表格、时间区间列表

备份文件路径名称

数据恢复脚本,见2.3


2.5  删除归档数据

truncatetable [dbo].[testpp2018]

————————————————

版权声明:本文为CSDN博主「三空道人」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:https://blog.csdn.net/zhoujunah/article/details/79665279

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 199,393评论 5 467
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 83,790评论 2 376
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 146,391评论 0 330
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 53,703评论 1 270
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 62,613评论 5 359
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,003评论 1 275
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,507评论 3 390
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,158评论 0 254
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,300评论 1 294
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,256评论 2 317
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,274评论 1 328
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 32,984评论 3 316
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,569评论 3 303
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,662评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,899评论 1 255
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 42,268评论 2 345
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 41,840评论 2 339