数据库备份还原顺序关系(环境: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:.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、查看备份文件关系

SELECT  a.database_name
       ,CASE a.type
          WHEN 'D' THEN 'full'
          WHEN 'I' THEN 'differential'
          WHEN 'L' THEN 'log'
          WHEN 'F' THEN 'file / filegroup'
          WHEN 'G' THEN 'differential file'
          WHEN 'P' THEN 'partial'
          WHEN 'Q' THEN 'differential partial'
        END AS [type]	--备份类型。可以是：D = 数据库 I = 差异数据库 L = 日志 F = 文件或文件组 G = 差异文件 P = 部分 Q = 差异部分 可以为 NULL。
       ,b.physical_device_name
       ,a.first_lsn
       ,a.last_lsn
       ,a.differential_base_lsn
FROM    msdb.dbo.backupset a
        INNER JOIN msdb.dbo.backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE   a.database_name = 'db'
ORDER BY a.backup_start_date
       ,b.family_sequence_number

3、还原顺序

USE master
go
--1. 恢复时使用错误的日志顺序
--1.1
RESTORE DATABASE db FROM DISK='d:.bak' WITH REPLACE;

--查看
SELECT * FROM db.dbo.Test
/*
ID
*/
go
--1.2
RESTORE DATABASE db FROM DISK='d:.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:.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:.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:.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:.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:.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:.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:.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:.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:.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
*/