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]

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

ALTER DATABASE [test] MODIFY FILEGROUP [data] DEFAULT

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;

--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;

为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]

从下图可以看到此时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]

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

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

从下图可以看到，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;

此时主文件大小仍然为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)

--为testpp表添加分区

USE [test]

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]

DBCC SHRINKFILE(N'test', 0) withno_infomsgs

--参数说明

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

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

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

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

二. 归档历史数据

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

--合并分区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]

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

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

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

2.4 归档说明

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

备份文件路径名称

数据恢复脚本，见2.3

2.5 删除归档数据

truncatetable [dbo].[testpp2018]

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

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