SQL Server中的高可用性（2）----文件與文件組

--創建分區函數
CREATE PARTITION FUNCTION [t](int) AS RANGE LEFT FOR VALUES (100, 500)
--分區架構
CREATE PARTITION SCHEME [x] AS PARTITION [t] TO ([PRIMARY], [FileGroup1], [FileGroup1])

--創建表
CREATE TABLE [Sales].[SalesOrderDetailPartition](
	[SalesOrderID] [int] NOT NULL,
	[SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
	[CarrierTrackingNumber] [nvarchar](25) NULL,
	[OrderQty] [smallint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[SpecialOfferID] [int] NOT NULL,
	[UnitPrice] [money] NOT NULL,
	[UnitPriceDiscount] [money] NOT NULL,
	[LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
) ON x(SalesOrderID)

INSERT 	INTO [Sales].[SalesOrderDetailPartition]([SalesOrderID],
	[CarrierTrackingNumber],
	[OrderQty] ,
	[ProductID] ,
	[SpecialOfferID],
	[UnitPrice],
	[UnitPriceDiscount],ModifiedDate,rowguid)
SELECT 	[SalesOrderID],
	[CarrierTrackingNumber],
	[OrderQty] ,
	[ProductID] ,
	[SpecialOfferID],
	[UnitPrice],
	[UnitPriceDiscount],ModifiedDate,rowguid FROM [Sales].[SalesOrderDetail]


go
--創建索引分區
CREATE NONCLUSTERED INDEX test_partition_idx ON [Sales].[SalesOrderDetailPartition](ProductID) on x(SalesOrderID)

--僅僅重建某個分區
ALTER INDEX test_partition_idx
ON [Sales].[SalesOrderDetailPartition]
REBUILD Partition = 1

--創建測試數據庫
CREATE DATABASE test
 GO
--改成完整恢復模式
ALTER DATABASE test SET  RECOVERY FULL
--添加一個文件組
ALTER DATABASE test
ADD FILEGROUP WW_GROUP
 GO
 --向文件組中添加文件
ALTER DATABASE test
ADD FILE 
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
 
 --在不同文件組上分別創建兩個表
CREATE TABLE test..test ( id INT IDENTITY )
ON  [primary]
CREATE TABLE test..test_GR ( id INT IDENTITY )
ON  ww_Group
 

--做完整備份
BACKUP DATABASE test
 TO DISK='D:\Test_backup.bak'WITH INIT

--做文件備份
BACKUP DATABASE test
    FILE = 'ww',
    FILEGROUP = 'ww_Group'
    TO DISK='D:\CROUPFILES.bak'WITH INIT
--備份日志
BACKUP LOG test
    TO DISK='D:\Test__log.ldf'WITH INIT

 
--刪除文件組中的表內的數據
TRUNCATE TABLE test..test_GR

--還原備份，日志僅僅被應用于那個還原狀態的文件
RESTORE DATABASE test
    FILE = 'ww',
    FILEGROUP = 'ww_Group'
    FROM DISK ='D:\CROUPFILES.bak'
    WITH FILE = 1,NORECOVERY
RESTORE LOG test
    FROM DISK='D:\Test__log.ldf'
    WITH FILE = 1, NORECOVERY

--備份尾端日志
BACKUP LOG test
    TO DISK='D:\Test__log.ldf' WITH NOINIT,NO_TRUNCATE
--還原尾端日志
RESTORE LOG test
    FROM DISK='D:\Test__log.ldf'
    WITH FILE = 2, RECOVERY
 GO

 --查看數據，刪除數據的操作被成功恢復
 SELECT  *
FROM    test..test_GR

--清除數據庫
DROP DATABASE test