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

来源：互联网发布：linux运维工程师培训编辑：程序博客网时间：2024/04/28 02:27
--测试环境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' , 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)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,indatedatetime 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,name nvarchar(50) not null,sex bit not null,age  smallint not null,indatedatetime 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 ,age) SELECT 'KK',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 style="font-family: Arial, Helvetica, sans-serif;">组</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
0 0