SqlServer 文件和文件组备份还原测试

--	测试环境
USE master
GO
--	DROP DATABASE [Demo]
CREATE DATABASE [Demo]
ON  PRIMARY		( NAME = N'Demo',FILENAME = N'D:MSSQLDATADemo.mdf',SIZE = 3072KB,FILEGROWTH = 1024KB ),FILEGROUP [FG1] ( NAME = N'Demo01',FILENAME = N'D:MSSQLDATADemo01.ndf',FILEGROUP [FG2] ( NAME = N'Demo02',FILENAME = N'D:MSSQLDATADemo02.ndf',FILEGROUP [FG3] ( NAME = N'Demo03',FILENAME = N'D:MSSQLDATADemo03.ndf',FILEGROWTH = 1024KB )
LOG ON			( NAME = N'Demo_log',FILENAME = N'D:MSSQLDATADemo_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,) 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,10
GO 5

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

use master
GO

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

--	（某个时间）备份文件Demo03
BACKUP DATABASE Demo  
   FILE = 'Demo03' 
   TO DISK = 'D:MSSQLDATAbackupDemo_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:MSSQLDATAbackupDemo_log.bck'
   WITH INIT,COMPRESSION
GO  

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

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


--	重做后来的日志
RESTORE LOG [Demo]
	FROM  DISK = N'D:MSSQLDATAbackupDemo_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:MSSQLDATAbackupDemo_filegroup_FG3.bck'
	WITH INIT,COMPRESSION
GO 

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


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

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

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


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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!