SQL Server Partition script backup

--Generate partition script
DECLARE @DataBaseName NVARCHAR (50)--database name
DECLARE @TableName NVARCHAR (50)--table name
DECLARE @ColumnName NVARCHAR (50)--field name
DECLARE @Location NVARCHAR (50)--The path to save the partition file
DECLARE @PStartDay date--partition start time point
DECLARE @PEndDay Date--partition cutoff point
DECLARE @FGStr NVARCHAR (50)--File group name
DECLARE @FStr NVARCHAR (50)--File name
DECLARE @Size NVARCHAR () = ' 10MB '--partition initialization size, default is 10MB
DECLARE @FileGrowth NVARCHAR () = ' 100MB '--partition file increment, default value is 100MB

DECLARE @sql NVARCHAR (max)

--Set the following variables
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. Creating filegroups

PRINT '--1. Create file Group '
SET @y = year (@PStartDay)
While @y <= year (@PEndDay)
BEGIN
SET @FGStr = @y
SET @sql = ' ALTER DATABASE [' [email protected] + '] ADD FILEGROUP [fg_ ' [email protected]+ '] '
PRINT @sql + CHAR (13)
SET @y = @y+1
END

--2. Creating a File

PRINT CHAR (13) + '--2. Create file '
SET @y = year (@PStartDay)
While @y <=year (@PEndDay)
BEGIN
SET @[email protected]
SET @[email protected]
SET @sql = ' ALTER DATABASE [' [email protected] + ']
ADD FILE
(NAME = n ' fg_ ' [email protected]+ ' _data ', FILENAME = n ' ' [email protected]+ ' fg_ ' [email protected]+ ' _data.ndf ', SIZE = ' [Email protected]+ ', filegrowth = ' [email protected]+ ')
To FILEGROUP [fg_ ' [email protected]+ ']; '
PRINT @sql + CHAR (13)
SET @y = @y+1
END

--3. Creating a Partition function
PRINT CHAR (13) + '--3. Create partition function '
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_ ' [Email protected]+ ' (date) as RANGE right for VALUES (' [email protected]+ ') '
PRINT @sql + CHAR (13)

--4. Creating a partitioning scheme
PRINT CHAR (13) + '--4. Create PARTITION scheme '

SET @y = year (@PStartDay)
SET @FGStr = ' '
While @y<=year (@PEndDay)
BEGIN
SET @FGStr [Email protected]+ ' [fg_ ' +cast (@y as nvarchar (4)) + '], '
SET @[email protected]+1
END
SET @FGStr =substring (@FGStr, 1,len (@FGStr)-1)
SET @sql = ' CREATE PARTITION SCHEME sch_ ' [email protected]+ ' AS
PARTITION fun_ ' [email protected]+ '
to (' [email protected]+ ') '
PRINT @sql + CHAR (13)

/***************************************************************************************
****************************************************************************************
* * Other statements filed
****************************************************************************************
****************************************************************************************/

--1. Adding a partition to a table
SET @sql = ' BEGIN TRANSACTION
CREATE CLUSTERED INDEX pindex on [dbo]. [' [Email protected]+ ']
(
[' [Email protected]+ ']
) with (sort_in_tempdb = off, drop_existing = off, ONLINE = off) on
[Sch_ ' [email protected]+ '] ([' [email protected]+ '])

DROP INDEX pindex on [dbo]. [' [Email protected]+ ']
COMMIT TRANSACTION
‘
Print @sql +char (13)

--Add File groups
ALTER DATABASE dbtest ADD filegroup [fg_2017]
--New data file
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 a partition function
CREATE PARTITION FUNCTION fun_datekey (int) as RANGE right for VALUES (20140101,20150101)
--Create a partition scheme
CREATE PARTITION SCHEME Sch_datekey as PARTITION fun_datekey to ([fg_2015],[fg_2016],[fg_2017])

--modifying partition functions and schemes
alter PARTITION scheme [Sch_fulldatealternatekey] Next used [fg_2017]
alter PARTITION function [Fun_fulldatealternatekey] () Split range (' 2016-01-01 ')

--Transfer partition data to a partition of another table
ALTER TABLE Dbtest.dbo.dimdate switch partition 2 to DBTEST.DBO.DIMDATE_TEMP partition 2

--Query partition function, partition scheme, System view of boundary value
SELECT * FROM Sys.partition_functions
SELECT * FROM Sys.partition_range_values
SELECT * FROM Sys.partition_schemes

SQL Server Partition script backup