--生成分区脚本
DECLARE @DataBaseName NVARCHAR(50)--数据库名称
DECLARE @TableName NVARCHAR(50)--表名称
DECLARE @ColumnName NVARCHAR(50)--字段名称
DECLARE @Location NVARCHAR(50)--保存分区文件的路径
DECLARE @PStartDay DATE--分区开始时间点
DECLARE @PEndDay DATE --分区截止时间点
DECLARE @FGStr NVARCHAR(50) --文件组名
DECLARE @FStr NVARCHAR(50) --文件名
DECLARE @Size NVARCHAR(50)=‘10MB‘--分区初始化大小,默认为10MB
DECLARE @FileGrowth NVARCHAR(50)=‘100MB‘--分区文件增量 ，默认值为100MB

DECLARE @sql NVARCHAR(max)

--设置下面变量
SET @DataBaseName = ‘BigDBTest‘
SET @TableName = ‘Dim_Date‘
SET @ColumnName = ‘DateKey‘
SET @Location = ‘H:\partion\‘
SET @PStartDay=‘2015-01-01‘
SET @PEndDay=‘2016-02-01‘
SET @FGStr = ‘‘
SET @FStr = ‘‘

DECLARE @Y INT

--1.创建文件组

PRINT ‘--1.创建文件组‘
SET @Y = YEAR(@PStartDay)
WHILE @Y <= YEAR(@PEndDay)
BEGIN
SET @FGStr = @Y
SET @sql = ‘ALTER DATABASE [‘+@DataBaseName +‘] ADD FILEGROUP [FG_‘+@FGStr+‘]‘
PRINT @sql + CHAR(13)
SET @Y = @Y+1
END

--2.创建文件

PRINT CHAR(13)+‘--2.创建文件‘
SET @Y = YEAR(@PStartDay)
WHILE @Y <=YEAR(@PEndDay)
BEGIN
SET @FGStr=@Y
SET @FStr=@Y
SET @sql = ‘ALTER DATABASE [‘+@DataBaseName +‘]
ADD FILE
(NAME = N‘‘FG_‘+@FStr+‘_data‘‘,FILENAME = N‘‘‘+@Location+‘FG_‘+@FStr+‘_data.ndf‘‘,SIZE = ‘+@Size+‘, FILEGROWTH = ‘+@FileGrowth+‘ )
TO FILEGROUP [FG_‘+@FGStr+‘];‘
PRINT @sql + CHAR(13)
SET @Y = @Y+1
END

--3.创建分区函数
PRINT CHAR(13)+‘--3.创建分区函数‘
DECLARE @FunValueStr NVARCHAR(MAX)=‘‘

SET @Y = YEAR(@PStartDay)
WHILE @Y <YEAR(@PEndDay)
BEGIN
SET @FunValueStr = @FunValueStr +‘‘‘‘+CAST(@Y AS NVARCHAR(4))+‘-01-01‘‘‘+‘,‘
SET @Y = @Y+1
END
SET @FunValueStr = SUBSTRING(@FunValueStr,1,len(@FunValueStr)-1)
SET @sql = ‘CREATE PARTITION FUNCTION
Fun_‘+@ColumnName+‘(date) AS RANGE RIGHT FOR VALUES(‘+@FunValueStr+‘)‘
PRINT @sql + CHAR(13)

--4.创建分区方案
PRINT CHAR(13)+‘--4.创建分区方案‘

SET @Y = YEAR(@PStartDay)
SET @FGStr = ‘‘
WHILE @Y<=YEAR(@PEndDay)
BEGIN
SET @FGStr =@FGStr+‘[FG_‘+CAST(@Y AS nvarchar(4))+‘],‘
SET @Y=@Y+1
END
SET @FGStr=SUBSTRING(@FGStr,1,len(@FGStr)-1)
SET @sql = ‘CREATE PARTITION SCHEME Sch_‘+@ColumnName+‘ AS
PARTITION Fun_‘+@ColumnName+‘
TO(‘+@FGStr+‘)‘
PRINT @sql + CHAR(13)

/***************************************************************************************
****************************************************************************************
**其他语句备案
****************************************************************************************
****************************************************************************************/

--1.为表添加分区
SET @sql=‘BEGIN TRANSACTION
CREATE CLUSTERED INDEX PIndex ON [dbo].[‘+@TableName+‘]
(
[‘+@ColumnName+‘]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON
[Sch_‘+@ColumnName+‘]([‘+@ColumnName+‘])

DROP INDEX PIndex ON [dbo].[‘+@TableName+‘]
COMMIT TRANSACTION
‘
print @sql+CHAR(13)

--新增文件组
alter database dbtest add filegroup [FG_2017]
--新增数据文件
alter database dbtest add file (name = N‘FG_2017_data‘,filename=N‘E:\Parctice\DBP\FG_2017_data.nf‘
,size=10MB,Filegrowth=100MB) to filegroup [FG_2017]

--创建分区函数
CREATE PARTITION FUNCTION Fun_DateKey(int) AS RANGE RIght FOR VALUES(20140101,20150101)
--创建分区方案
CREATE PARTITION SCHEME Sch_DateKey AS PARTITION Fun_DateKey TO ([FG_2015],[FG_2016],[FG_2017])

--修改分区函数和方案
alter partition scheme [Sch_FullDateAlternateKey] next used [FG_2017]
alter partition function [Fun_FullDateAlternateKey]() split range(‘2016-01-01‘)

--将分区的数据转移到另外一个表的分区
alter table dbtest.dbo.dimdate switch partition 2 to dbtest.dbo.dimdate_temp partition 2

--查询分区函数、分区方案、边界值的系统视图
select * from sys.partition_functions
select * from sys.partition_range_values
select * from sys.partition_schemes

SQL Server分区脚本备份