SQL Server数据归档的解决方案

sql server 表分区操作

创建一个测试数据库

USE Master;
GO
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1',
FILENAME=
'D:\TestData\Primary\TestDB_Part1.mdf',
SIZE=10,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2',
FILENAME =
'D:\TestData\Secondary\TestDB_Part2.ndf',
SIZE = 10,
MAXSIZE=100,
FILEGROWTH=1 );
GO

在原有数据库上增加文件和文件组

ALTER DATABASE SRM ADD FILEGROUP ARCHIVE
  --增加文件组
ALTER DATABASE SRM ADD FILE   --增加文件
(
NAME=archive_file,
FILENAME='d:\SQLDB\archive_file.ndf',
SIZE=10MB,
FILEGROWTH=10%
)
TO FILEGROUP archive

新建分区函数，参数类型是bit，即已归档的数据（rangeleft：小于等于，rangeright ：大于等于）

Create Partition Function TestDB_ArchivePartitionRange(bit) AS RANGE right FOR VALUES(1)

新建一个分区方案，即已经归档的数据保存到TestDB_Part2分区文件上

CREATE Partition Scheme TestDB_ArchivePatitionScheme AS PARTITION TestDB_ArchivePartitionRange TO ([PRIMARY], TestDB_Part2);

创建一个测试数据表，绑定一个分区方案

CREATE TABLE TestArchiveTable  (Archived Bit NOT NULL, Date DATETIME)  ON TestDB_ArchivePatitionScheme (Archived)

把普通表转换为分区表

-- 1. 删除索引  
--    a. 删除存储历史存档记录的表中的索引  
alter table byc_inventory drop constraint i_barcode


-- 2. 转换为分区表  
--    a. 将存储历史存档记录的表转换为分区表  
ALTER TABLE byc_inventory 
    DROP CONSTRAINT PK_BYC_INVENTORY  
    WITH(  
        MOVE TO WMS_ArchivePatitionScheme(Archived))
-- 3. 恢复主键  
--    a. 恢复存储历史存档记录的分区表的主键
ALTER TABLE byc_inventory  
    ADD CONSTRAINT PK_BYC_INVENTORY  
        PRIMARY KEY CLUSTERED(  
            id,Archived)
            
--4. 恢复唯一索引
alter table byc_inventory add constraint i_barcode unique (i_barcode,Archived)

把分区表转为普通表，首先删除分区表的主键和索引，然后执行语句创建分区列索引，最后创建普通表主键和索引

CREATE CLUSTERED INDEX IX_Archived ON byc_inventory(Archived)  
ON [PRIMARY]  
GO

http://www.cnblogs.com/knowledgesea/p/3696912.html

http://blog.youkuaiyun.com/netyeaxi/article/details/75091373

https://docs.microsoft.com/zh-cn/sql/relational-databases/partitions/modify-a-partition-function

http://www.cnblogs.com/knowledgesea/p/3696912.html

http://blog.youkuaiyun.com/ccyyss/article/details/32711579

http://www.cnblogs.com/qq260250932/p/5479037.html