首页 > 数据库 > SQL Server >

数据库备份还原顺序关系(环境:MicrosoftSQLServer2008R2)

2014-09-20

让新手们了解一下备份顺序--1、塔建环境(生成测试数据和备份文件) * 测试环境: Microsoft SQL Server 2008 R2 (RTM) - 10 50 1600 1 (X64) Apr 2 2010 15:48:46 Copyright (c) Micros

让新手们了解一下备份顺序

--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=&#39;d:\0.bak&#39; WITH FORMAT
GO
--1
INSERT test SELECT 1
go	
--生成备份文件 1.trn	
BACKUP LOG db TO DISK=&#39;d:\1.trn&#39; WITH FORMAT
go
--2
INSERT test SELECT 2	
go
--生成备份文件 2.trn
BACKUP LOG db TO DISK=&#39;d:\2.trn&#39; WITH FORMAT
go
--3
INSERT test SELECT 3	
go
--生成备份文件 3.dif
BACKUP DATABASE db TO DISK=&#39;d:\3.dif&#39; WITH FORMAT,DIFFERENTIAL
go
--4
INSERT test SELECT 4	
go
--生成备份文件 4.trn
BACKUP LOG db TO DISK=&#39;d:\4.trn&#39; WITH FORMAT
--5
INSERT test SELECT 5	
go
--生成备份文件 5.dif
BACKUP DATABASE db TO DISK=&#39;d:\5.dif&#39; WITH FORMAT,DIFFERENTIAL
--6
INSERT test SELECT 6	

--生成备份文件 6.trn
BACKUP LOG db TO DISK=&#39;d:\6.trn&#39; WITH FORMAT

--7
INSERT test SELECT 7
	
--生成备份文件 7.trn
BACKUP LOG db TO DISK=&#39;d:\7.trn&#39; 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=&#39;d:\0.bak&#39; WITH REPLACE;

--查看
SELECT * FROM db.dbo.Test
/*
ID
*/
go
--1.2
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\1.trn&#39; 

--查看
SELECT * FROM db.dbo.Test
/*
ID
1
*/
go
--1.3
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\1.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\2.trn&#39; 
--查看
SELECT * FROM db.dbo.Test
/*
ID
1
2
*/
go
--1.4
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE DATABASE db FROM DISK=&#39;d:\3.dif&#39;
--查看
SELECT * FROM db.dbo.Test
/*
ID
1
2
3
*/
go
--1.5
--1.5.1
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE DATABASE db FROM DISK=&#39;d:\3.dif&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\4.trn&#39;
--查看
SELECT * FROM db.dbo.Test
/*
ID
1
2
3
4
*/
GO
--1.5.2
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE DATABASE db FROM DISK=&#39;d:\1.trn&#39; WITH NORECOVERY
RESTORE DATABASE db FROM DISK=&#39;d:\2.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\4.trn&#39;
--查看
SELECT * FROM db.dbo.Test
/*
ID
1
2
3
4
*/
go
--1.6
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE DATABASE db FROM DISK=&#39;d:\5.dif&#39; 
--查看
SELECT * FROM db.dbo.Test
/*
ID
1
2
3
4
5
*/
go
--1.7
--1.7.1
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE DATABASE db FROM DISK=&#39;d:\5.dif&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\6.trn&#39; 
--查看
SELECT * FROM db.dbo.Test
/*
ID
1
2
3
4
5
6
*/
go
--1.7.2
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\1.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\2.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\4.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\6.trn&#39; 
--查看
SELECT * FROM db.dbo.Test
/*
ID
1
2
3
4
5
6
*/
go
--1.8
--1.8.1
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE DATABASE db FROM DISK=&#39;d:\5.dif&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\6.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\7.trn&#39;
--查看
SELECT * FROM db.dbo.Test
/*
ID
1
2
3
4
5
6
7
*/
go
--1.8.2
RESTORE DATABASE db FROM DISK=&#39;d:\0.bak&#39; WITH REPLACE,NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\1.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\2.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\4.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\6.trn&#39; WITH NORECOVERY
RESTORE LOG db FROM DISK=&#39;d:\7.trn&#39;
--查看
SELECT * FROM db.dbo.Test
/*
ID
1
2
3
4
5
6
7
*/
相关文章
最新文章
热点推荐