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]
droptable [dbo].[testpp2018]

如果喜欢，可以扫码关注SQL Server 公众号，将有更多精彩内容分享：