T-SQL 备份和还原

Code

/*
Author:Terry.Sai.M.J 浪客
Location:BeiJing
DateTime:GETDATE()
Description:数据库的段落还原（完整恢复模式）
*/

IF DB_ID('db') IS NOT NULL
　　　 DROP DATABASE db
GO

--创建包含多个文件组的数据库db
CREATE DATABASE db
ON PRIMARY
(
　　　 NAME=db_data,
　　　 FILENAME='c:\db_data.mdf'
),
FILEGROUP A
(
　　　 NAME=db_data_a,
　　　 FILENAME='c:\db_data_a.ndf'
),
FILEGROUP B
(
　　　 NAME=db_data_b,
　　　 FILENAME='c:\db_data_b.ndf'
),
FILEGROUP C
(
　　　 NAME=db_data_c,
　　　 FILENAME='c:\db_data_c.ndf'
)
LOG ON
(
　　　 NAME=db_log,
　　　 FILENAME='c:\db_log.ldf'　　　
)
GO

--在主文件组下创建表
CREATE TABLE db.dbo.T(ID INT PRIMARY KEY IDENTITY(1,1)) ON [PRIMARY];

--备份主文件组PRIMARY
BACKUP DATABASE db FILEGROUP='PRIMARY' TO DISK='c:\db_data.bak' WITH FORMAT;

INSERT INTO db.dbo.T DEFAULT VALUES

--备份当前日志
BACKUP LOG db TO DISK='c:\db_data_1.bak' WITH FORMAT;

GO

--在B文件组下面创建表
CREATE TABLE db.dbo.T1(ID INT PRIMARY KEY IDENTITY(1,1)) ON [B];

--备份文件组B
BACKUP DATABASE db FILEGROUP='B' TO DISK='c:\db_data_b_1.bak' WITH FORMAT;

INSERT INTO db.dbo.T1 DEFAULT VALUES

--备份当前日志
BACKUP LOG db TO DISK='c:\db_data_2.bak' WITH FORMAT;

GO

--备份C
BACKUP DATABASE db FILEGROUP='C' TO DISK='c:\db_data_c.bak' WITH FORMAT;

--备份A
BACKUP DATABASE db FILEGROUP='A' TO DISK='c:\db_data_a.bak' WITH FORMAT;

INSERT INTO db.dbo.T1 DEFAULT VALUES

--备份当前日志
BACKUP LOG db TO DISK='c:\db_data_b.bak' WITH FORMAT;

GO

--模拟破坏,删除其中一个ndf,然后制定NORECOVERY关键字,使数据库处于RESTOING状态,NO_TRUNCATE表示损坏还能备份
BACKUP LOG db TO DISK='c:\db_log.bak' WITH NORECOVERY, NO_TRUNCATE,FORMAT
GO

--首先恢复主文件组,注意现在是段落还原,所以要制定PARTIAL,而且只能出现一次该关键字.
RESTORE DATABASE db FILEGROUP='PRIMARY' FROM DISK='c:\db_data.bak' WITH PARTIAL,NORECOVERY;

--恢复所有日志,注意先后顺序,日志是不区分文件组,所以全部都要恢复
RESTORE LOG db FROM DISK='c:\db_data_1.bak' WITH NORECOVERY;
RESTORE LOG db FROM DISK='c:\db_data_2.bak' WITH NORECOVERY;
RESTORE LOG db FROM DISK='c:\db_data_b.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY

--结果正确 ,返回1行记录
SELECT * FROM db.dbo.T

--出错,因为段落未还原的文件组,都处于离线状态
SELECT * FROM db.dbo.T1
GO

--恢复A文件组,还原日志,注意顺序,因为LOG的LSN肯定要晚于文件组A的备份
RESTORE DATABASE db FILEGROUP='A' FROM DISK='c:\db_data_a.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_data_b.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY
GO

--恢复B文件组,还原日志,注意顺序,因为LOG的LSN肯定要晚于文件组B的备份
RESTORE DATABASE db FILEGROUP='B' FROM DISK='c:\db_data_b_1.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_data_2.bak' WITH NORECOVERY;
RESTORE LOG db FROM DISK='c:\db_data_b.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY

SELECT * FROM db.dbo.T1
GO

--恢复C文件组,还原日志,注意顺序,因为LOG的LSN肯定要晚于文件组C的备份
RESTORE DATABASE db FILEGROUP='C' FROM DISK='c:\db_data_c.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_data_b.bak' WITH NORECOVERY
RESTORE LOG db FROM DISK='c:\db_log.bak' WITH RECOVERY
GO

--删除测试数据库
DROP DATABASE db
GO