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

概述-- 测试环境USE masterGO-- DROP DATABASE [Demo]CREATE DATABASE [Demo]ON PRIMARY ( NAME = N'Demo', FILENAME = N'D:\MSSQLDATA\Demo.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB ), FILEGROUP [FG1] ( NAME

--	测试环境USE masterGO--	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',fileGROUP [FG2] ( name = N'Demo02',filename = N'D:\MSsqlDATA\Demo02.ndf',fileGROUP [FG3] ( name = N'Demo03',filename = N'D:\MSsqlDATA\Demo03.ndf',fileGROWTH = 1024KB )LOG ON			( name = N'Demo_log',filename = N'D:\MSsqlDATA\Demo_log.ldf',SIZE = 1024KB,fileGROWTH = 10MB)GOALTER DATABASE [Demo] SET RECOVERY FulL WITH NO_WAITGOUSE [Demo]GO--	drop table dbo.TABCREATE 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]GOALTER table dbo.TAB ADD CONSTRAINT [DF_TAB_GuID] DEFAulT(NEWSEQUENTIALID()) FOR [GuID]GOALTER table dbo.TAB ADD CONSTRAINT [DF_TAB_sex] DEFAulT(1) FOR [sex]GOALTER table dbo.TAB ADD CONSTRAINT [DF_TAB_indate] DEFAulT(GETDATE()) FOR [indate]GOUSE [Demo]GO--	drop table dbo.TAB2CREATE table dbo.TAB2(	GuID uniqueIDentifIEr not null,) ON [PRIMARY]GOALTER table dbo.TAB2 ADD CONSTRAINT [DF_TAB2_GuID] DEFAulT(NEWSEQUENTIALID()) FOR [GuID]GOALTER table dbo.TAB2 ADD CONSTRAINT [DF_TAB2_sex] DEFAulT(1) FOR [sex]GOALTER table dbo.TAB2 ADD CONSTRAINT [DF_TAB2_indate] DEFAulT(GETDATE()) FOR [indate]GOINSERT INTO Demo.dbo.TAB(name,age) SELECT 'KK',10GO 5INSERT INTO Demo.dbo.TAB2(name,10GO 5--	TAB 在文件组 FG3(Demo03) ； TAB2 在文件组 PRIMARY .use masterGO--------------------------------------------------------/*************** 数据库文件备份还原测试 ***************/----------------------------------------------------------	（某个时间）备份文件Demo03BACKUP DATABASE Demo     file = 'Demo03'    TO disK = 'D:\MSsqlDATA\backup\Demo_file_Demo03.bck'   WITH INIT,COMPRESSIONGO--	更改数据UPDATE Demo.dbo.TAB SET name='OO'GOUPDATE Demo.dbo.TAB2 SET name='OO'GO--	备份当前日志BACKUP LOG Demo     TO disK = 'D:\MSsqlDATA\backup\Demo_log.bck'   WITH INIT,COMPRESSIONGO  --	还原文件 Demo03RESTORE DATABASE [Demo]	file = N'Demo03'	FROM  disK = N'D:\MSsqlDATA\backup\Demo_file_Demo03.bck'	WITH  norECOVERYGO/*此时文件组 FG3(Demo03)不能访问，但其他文件组的表可以正常访问！*/--	重做后来的日志RESTORE LOG [Demo]	FROM  disK = N'D:\MSsqlDATA\backup\Demo_log.bck'	WITH  RECOVERYGO--	完成！SELECT * FROM Demo.dbo.TABSELECT * FROM Demo.dbo.TAB2--------------------------------------------------------/************* 数据库文<span >组</span>备份还原测试 ***************/----------------------------------------------------------	（某个时间）备份文件 FG3BACKUP DATABASE Demo  	fileGROUP = N'FG3'	TO disK = N'D:\MSsqlDATA\backup\Demo_filegroup_FG3.bck'	WITH INIT,COMPRESSIONGO --	更改 TAB2 数据UPDATE Demo.dbo.TAB2 SET name=''GO--	备份当前日志BACKUP LOG Demo     TO disK = 'D:\MSsqlDATA\backup\Demo_log.bck'   WITH INIT,COMPRESSIONGO  --	还原文件 Demo03RESTORE DATABASE [Demo]	fileGROUP = N'FG3'	FROM  disK = N'D:\MSsqlDATA\backup\Demo_filegroup_FG3.bck'	WITH  norECOVERYGO/*此时文件组 FG3 不能访问，但其他文件组的表可以正常访问！*/--	重做后来的日志RESTORE LOG [Demo]	FROM  disK = N'D:\MSsqlDATA\backup\Demo_log.bck'	WITH  RECOVERYGO--	完成！SELECT * FROM Demo.dbo.TABSELECT * FROM Demo.dbo.TAB2

总结

以上是内存溢出为你收集整理的SqlServer 文件和文件组备份还原测试全部内容，希望文章能够帮你解决SqlServer 文件和文件组备份还原测试所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。