Database backup restore sequence relationship (environment: Microsoft SQL Server R2)

Let novices understand the backup sequence

--1, tower construction environment (generate test data and backup files)

/ *
test environment:
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 test
CREATE DATABASE db
GO

USE db
GO
CREATE TABLE Test (ID INT);

--Generate backup file 0.bak
BACKUP DATABASE db TO DISK = ‘d: \ 0.bak’ WITH FORMAT
GO
--1
INSERT test SELECT 1
go
--Generate backup file 1.trn
BACKUP LOG db TO DISK = ‘d: \ 1.trn’ WITH FORMAT
go
--2
INSERT test SELECT 2
go
--Generate backup file 2.trn
BACKUP LOG db TO DISK = ‘d: \ 2.trn’ WITH FORMAT
go
--3
INSERT test SELECT 3
go
-Generate backup file 3.dif
BACKUP DATABASE db TO DISK = ‘d: \ 3.dif’ WITH FORMAT, DIFFERENTIAL
go
--4
INSERT test SELECT 4
go
-Generate backup file 4.trn
BACKUP LOG db TO DISK = ‘d: \ 4.trn’ WITH FORMAT
--5
INSERT test SELECT 5
go
-Generate backup file 5.dif
BACKUP DATABASE db TO DISK = ‘d: \ 5.dif’ WITH FORMAT, DIFFERENTIAL
--6
INSERT test SELECT 6

-Generate backup file 6.trn
BACKUP LOG db TO DISK = ‘d: \ 6.trn’ WITH FORMAT

--7
INSERT test SELECT 7
The
--Generate backup file 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. Restore sequence

USE master
go
--1. Use wrong log sequence when recovering
--1.1
RESTORE DATABASE db FROM DISK = ‘d: \ 0.bak’ WITH REPLACE;

--View
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’

--View
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’
--View
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’
--View
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’
--View
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’
--View
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’
--View
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’
--View
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’
--View
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’
--View
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’
--View
SELECT * FROM db.dbo.Test
/ *
Id
1
2
3
4
5
6
7
* /




Database backup and restore order relationship (environment: Microsoft SQL Server 2008 R2)