数据库备份还原顺序关系(环境:Microsoft SQL Server 2008 R2)
让新手们了解一下备份顺序
--1、塔建环境(生成测试数据和备份文件)
/* 测试环境: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) */ USE master go --创建测试 CREATE DATABASE db GO USE db GO CREATE TABLE Test(ID INT); --生成备份文件 0.bak BACKUP DATABASE db TO DISK=‘d:\0.bak‘ WITH FORMAT GO --1 INSERT test SELECT 1 go --生成备份文件 1.trn BACKUP LOG db TO DISK=‘d:\1.trn‘ WITH FORMAT go --2 INSERT test SELECT 2 go --生成备份文件 2.trn BACKUP LOG db TO DISK=‘d:\2.trn‘ WITH FORMAT go --3 INSERT test SELECT 3 go --生成备份文件 3.dif BACKUP DATABASE db TO DISK=‘d:\3.dif‘ WITH FORMAT,DIFFERENTIAL go --4 INSERT test SELECT 4 go --生成备份文件 4.trn BACKUP LOG db TO DISK=‘d:\4.trn‘ WITH FORMAT --5 INSERT test SELECT 5 go --生成备份文件 5.dif BACKUP DATABASE db TO DISK=‘d:\5.dif‘ WITH FORMAT,DIFFERENTIAL --6 INSERT test SELECT 6 --生成备份文件 6.trn BACKUP LOG db TO DISK=‘d:\6.trn‘ WITH FORMAT --7 INSERT test SELECT 7 --生成备份文件 7.trn BACKUP LOG db TO DISK=‘d:\7.trn‘ WITH FORMAT GO -- SELECT * FROM dbo.Test /* ID 1 2 3 4 5 6 7 */
2、还原顺序
USE master go --1. 恢复时使用错误的日志顺序 --1.1 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE; --查看 SELECT * FROM db.dbo.Test /* ID */ go --1.2 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE LOG db FROM DISK=‘d:\1.trn‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 */ go --1.3 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE LOG db FROM DISK=‘d:\1.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\2.trn‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 2 */ go --1.4 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK=‘d:\3.dif‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 */ go --1.5 --1.5.1 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK=‘d:\3.dif‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\4.trn‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 */ GO --1.5.2 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK=‘d:\1.trn‘ WITH NORECOVERY RESTORE DATABASE db FROM DISK=‘d:\2.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\4.trn‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 */ go --1.6 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK=‘d:\5.dif‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 */ go --1.7 --1.7.1 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK=‘d:\5.dif‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\6.trn‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 6 */ go --1.7.2 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE LOG db FROM DISK=‘d:\1.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\2.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\4.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\6.trn‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 6 */ go --1.8 --1.8.1 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE DATABASE db FROM DISK=‘d:\5.dif‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\6.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\7.trn‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 6 7 */ go --1.8.2 RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERY RESTORE LOG db FROM DISK=‘d:\1.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\2.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\4.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\6.trn‘ WITH NORECOVERY RESTORE LOG db FROM DISK=‘d:\7.trn‘ --查看 SELECT * FROM db.dbo.Test /* ID 1 2 3 4 5 6 7 */
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。