【转载】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
*/
posted @ 2012-12-19 13:05  唔愛吃蘋果  阅读(184)  评论(0编辑  收藏  举报