SQL Server分區指令碼備份

標籤：fst   online   設定   sql   --   bst   nvarchar   路徑   index   

--產生分區指令碼
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 [‘[email protected] +‘] ADD FILEGROUP [FG_‘[email protected]+‘]‘
PRINT @sql + CHAR(13)
SET @Y = @Y+1
END

--2.建立檔案

PRINT CHAR(13)+‘--2.建立檔案‘
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.建立分區函數
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_‘[email protected]+‘(date) AS RANGE RIGHT FOR VALUES(‘[email protected]+‘)‘
PRINT @sql + CHAR(13)

--4.建立資料分割配置
PRINT CHAR(13)+‘--4.建立資料分割配置‘

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)

/***************************************************************************************
****************************************************************************************
**其他語句備案
****************************************************************************************
****************************************************************************************/

--1.為表添加分區
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)

--新增檔案組
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分區指令碼備份