-- 测试环境
USE master
GO
-- DROP DATABASE [Demo]
CREATE DATABASE [Demo]
ON PRIMARY ( NAME = N'Demo', FILENAME = N'D:\MSSQLDATA\Demo.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG1] ( NAME = N'Demo01', FILENAME = N'D:\MSSQLDATA\Demo01.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG2] ( NAME = N'Demo02', FILENAME = N'D:\MSSQLDATA\Demo02.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG3] ( NAME = N'Demo03', FILENAME = N'D:\MSSQLDATA\Demo03.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'Demo_log', FILENAME = N'D:\MSSQLDATA\Demo_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10MB)
GO
ALTER DATABASE [Demo] SET RECOVERY FULL WITH NO_WAIT
GO

USE [Demo]
GO
-- drop table dbo.TAB
CREATE TABLE dbo.TAB(
Guid uniqueidentifier not null,
name nvarchar(50) not null,
sex bit not null,
age smallint not null,
indate datetime not null,
--CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED ([Guid] ASC)ON [FG3]
) ON [FG3]
GO
ALTER TABLE dbo.TAB ADD CONSTRAINT [DF_TAB_Guid] DEFAULT(NEWSEQUENTIALID()) FOR [Guid]
GO
ALTER TABLE dbo.TAB ADD CONSTRAINT [DF_TAB_sex] DEFAULT(1) FOR [sex]
GO
ALTER TABLE dbo.TAB ADD CONSTRAINT [DF_TAB_indate] DEFAULT(GETDATE()) FOR [indate]
GO

USE [Demo]
GO
-- drop table dbo.TAB2
CREATE TABLE dbo.TAB2(
Guid uniqueidentifier not null,
name nvarchar(50) not null,
sex bit not null,
age smallint not null,
indate datetime not null,
) ON [PRIMARY]
GO
ALTER TABLE dbo.TAB2 ADD CONSTRAINT [DF_TAB2_Guid] DEFAULT(NEWSEQUENTIALID()) FOR [Guid]
GO
ALTER TABLE dbo.TAB2 ADD CONSTRAINT [DF_TAB2_sex] DEFAULT(1) FOR [sex]
GO
ALTER TABLE dbo.TAB2 ADD CONSTRAINT [DF_TAB2_indate] DEFAULT(GETDATE()) FOR [indate]
GO

INSERT INTO Demo.dbo.TAB(name ,age) SELECT 'KK',10
GO 5
INSERT INTO Demo.dbo.TAB2(name ,age) SELECT 'KK',10
GO 5

-- TAB 在文件组 FG3(Demo03) ; TAB2 在文件组 PRIMARY .

use master
GO

--------------------------------------------------------
/*************** 数据库文件备份还原测试 ***************/
--------------------------------------------------------

-- (某个时间)备份文件Demo03
BACKUP DATABASE Demo
FILE = 'Demo03'
TO DISK = 'D:\MSSQLDATA\backup\Demo_file_Demo03.bck'
WITH INIT , COMPRESSION
GO

-- 更改数据
UPDATE Demo.dbo.TAB SET name='OO'
GO
UPDATE Demo.dbo.TAB2 SET name='OO'
GO

-- 备份当前日志
BACKUP LOG Demo
TO DISK = 'D:\MSSQLDATA\backup\Demo_log.bck'
WITH INIT , COMPRESSION
GO

-- 还原文件 Demo03
RESTORE DATABASE [Demo]
FILE = N'Demo03'
FROM DISK = N'D:\MSSQLDATA\backup\Demo_file_Demo03.bck'
WITH NORECOVERY
GO

/*此时文件组 FG3(Demo03)不能访问,但其他文件组的表可以正常访问!*/


-- 重做后来的日志
RESTORE LOG [Demo]
FROM DISK = N'D:\MSSQLDATA\backup\Demo_log.bck'
WITH RECOVERY
GO

-- 完成!
SELECT * FROM Demo.dbo.TAB
SELECT * FROM Demo.dbo.TAB2



--------------------------------------------------------
/************* 数据库文<span style="font-family: Arial, Helvetica, sans-serif;">组</span>备份还原测试 ***************/
--------------------------------------------------------

-- (某个时间)备份文件 FG3
BACKUP DATABASE Demo
FILEGROUP = N'FG3'
TO DISK = N'D:\MSSQLDATA\backup\Demo_filegroup_FG3.bck'
WITH INIT , COMPRESSION
GO

-- 更改 TAB2 数据
UPDATE Demo.dbo.TAB2 SET name=''
GO


-- 备份当前日志
BACKUP LOG Demo
TO DISK = 'D:\MSSQLDATA\backup\Demo_log.bck'
WITH INIT , COMPRESSION
GO

-- 还原文件 Demo03
RESTORE DATABASE [Demo]
FILEGROUP = N'FG3'
FROM DISK = N'D:\MSSQLDATA\backup\Demo_filegroup_FG3.bck'
WITH NORECOVERY
GO

/*此时文件组 FG3 不能访问,但其他文件组的表可以正常访问!*/


-- 重做后来的日志
RESTORE LOG [Demo]
FROM DISK = N'D:\MSSQLDATA\backup\Demo_log.bck'
WITH RECOVERY
GO

-- 完成!
SELECT * FROM Demo.dbo.TAB
SELECT * FROM Demo.dbo.TAB2