--创建分区函数
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