【转载】SQL2005差异备份

 

-- 创建测试数据库和测试表
CREATE DATABASE db_test
GO
CREATE TABLE db_test.dbo.tb(
id int)
INSERT db_test.dbo.tb (id)
VALUES (1)
GO

-- 创建完全备份
BACKUP DATABASE db_test
TO DISK = 'c:\db_test.bak'
WITH FORMAT

-- 插第1条测试数据
INSERT db_test.dbo.tb (id)
VALUES (12)
-- 创建差异备份
BACKUP DATABASE db_test
TO DISK = 'c:\db_test.bak'
WITH DIFFERENTIAL

-- 插第2条测试数据
INSERT db_test.dbo.tb (id)
VALUES (123)
-- 创建差异备份
BACKUP DATABASE db_test
TO DISK = 'c:\db_test.bak'
WITH DIFFERENTIAL
GO

-- 还原测试

-- 1. 只还原第1个差异文件
DROP DATABASE db_test
GO
RESTORE DATABASE db_test
FROM DISK = 'c:\db_test.bak'
WITH FILE = 1,
NORECOVERY
RESTORE DATABASE db_test
FROM DISK = 'c:\db_test.bak'
WITH FILE = 2
GO
SELECT * FROM db_test.dbo.tb
GO

-- 2. 只还原第2个差异文件
DROP DATABASE db_test
GO
RESTORE DATABASE db_test
FROM DISK = 'c:\db_test.bak'
WITH FILE = 1,
NORECOVERY
RESTORE DATABASE db_test
FROM DISK = 'c:\db_test.bak'
WITH FILE = 3
GO
SELECT * FROM db_test.dbo.tb
GO

-- 删除测试环境
DROP DATABASE db_test

--验证备份文件中第二个备份集是否有误

RESTORE VERIFYONLY
FROM DISK = 'c:\db_test.bak'
WITH FILE = 2

--注意：如果未指定file，默认只校验第一个备份集是否正确。

--查看备份设备上所有备份集的所有备份标头信息的结果集
RESTORE HEADERONLY
FROM  DISK = 'C:\db_test.bak'
/*
其中：
BackupType为备份类型：
1 = 数据库
2 = 事务日志
4 = 文件
5 = 差异数据库
6 = 差异文件
Position备份集在卷中的位置，用于 FILE = 选项。
BackupSize为备份大小（以字节为单位）
其他详见：http://technet.microsoft.com/zh-cn/library/ms178536(SQL.90).aspx
*/