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

Let's take a look at the backup sequence Novice

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

/* Test environment: Microsoft SQL Server R2 (RTM)-10.50.1600.1 (X64) APR 2 15:48:46 Copyright (c) Microsoft Corporatio N Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (build 7601:service Pack 1) */use mastergo--Create test Create Datab ASE dbgouse dbgocreate TABLE Test (ID INT); --Generate backup file 0.bakBACKUP DATABASE db to disk= ' D:\0.bak ' with Formatgo--1insert test SELECT 1go--Generate backup file 1.trnBACKUP LOG db to D  isk= ' d:\1.trn ' with Formatgo--2insert test SELECT 2go--Generate backup file 2.trnBACKUP LOG db to disk= ' d:\2.trn ' with Formatgo--3insert Test Select 3go--generates a backup file 3.difBACKUP DATABASE to disk= ' d:\3.dif ' with Format,differentialgo--4insert test select 4go--Health Cheng Bei file 4.trnBACKUP LOG db to disk= ' d:\4.trn ' with Format--5insert test SELECT 5go--Generate backup file 5.difBACKUP DATABASE db to disk= ' D:\5.dif ' with Format,differential--6insert test SELECT 6--Generate backup file 6.trnBACKUP LOG db to disk= ' d:\6.trn ' with Format--7inse RT Test SELECT 7--generates a backup file of 7.trnBACKUP LOG db to disk= ' d:\7.trn ' with Formatgo--select * FROM dbo. Test/*id1234567*/ 

2. View Backup file Relationships

SELECT A.database_name, Case a.type if ' D ' then ' the ' when ' I ' then          ' differential          ' when ' L ' then ' Log ' when          ' F ' and ' File/filegroup ' when          ' G ' and ' differential file ' when          ' P ' and ' partial ' when '          Q ' Then ' differential partial '        END as [type]--backup type.
can be: D = database I = differential database L = Log F = file or filegroup G = diff File P = part Q = Difference part can be null.       , B.physical_device_name       , A.first_lsn       , A.last_lsn       , A.differential_base_lsnfrom    Msdb.dbo.backupset a        INNER JOIN msdb.dbo.backupmediafamily b on a.media_set_id = B.media_set_idwhere   A.database_name = ' db ' ORDER by A.backup_start_date       , B.family_sequence_number

3. Restore sequence

Use Mastergo--1. Restore with incorrect log order--1.1restore DATABASE db from disk= ' D:\0.bak ' with replace;--view select * from db.dbo.test/*id*/ Go--1.2restore DATABASE db from disk= ' D:\0.bak ' with Replace,norecoveryrestore LOG db from disk= ' d:\1.trn '--View SELECT * FR OM db.dbo.test/*id1*/go--1.3restore DATABASE db from disk= ' D:\0.bak ' with Replace,norecoveryrestore LOG db from disk= ' D:\ 1.trn ' with Norecoveryrestore LOG db from disk= ' d:\2.trn '--View select * FROM Db.dbo.test/*id12*/go--1.4restore DATABASE db From disk= ' D:\0.bak ' with Replace,norecoveryrestore DATABASE db from disk= ' d:\3.dif '--View select * FROM db.dbo.test/*id123 */go--1.5--1.5.1restore database db from disk= ' D:\0.bak ' and Replace,norecoveryrestore DATABASE db from disk= ' D:\3.dif ' With Norecoveryrestore LOG db from disk= ' d:\4.trn '--View select * from Db.dbo.test/*id1234*/go--1.5.2restore DATABASE db FRO M disk= ' D:\0.bak ' with Replace,norecoveryrestore database db from disk= ' d:\1.trn ' with Norecoveryrestore database db from disk= ' d:\2.trn ' with NorecovEryrestore LOG db from disk= ' d:\4.trn '--View select * from Db.dbo.test/*id1234*/go--1.6restore DATABASE db from disk= ' d:\0.b AK ' with Replace,norecoveryrestore DATABASE db from disk= ' d:\5.dif '--View select * FROM db.dbo.test/*id12345*/go--1.7--1.7 .1RESTORE database db from disk= ' D:\0.bak ' with Replace,norecoveryrestore DATABASE db from disk= ' D:\5.dif ' with NORECOVERY RESTORE LOG db from disk= ' d:\6.trn '--View select * from Db.dbo.test/*id123456*/go--1.7.2restore DATABASE db from disk= ' d:\0. Bak ' with Replace,norecoveryrestore log db from disk= ' d:\1.trn ' with Norecoveryrestore LOG db from disk= ' d:\2.trn ' with NO Recoveryrestore log db from disk= ' d:\4.trn ' and Norecoveryrestore LOG db from disk= ' d:\6.trn '--View select * FROM Db.dbo.Te St/*id123456*/go--1.8--1.8.1restore database db from disk= ' D:\0.bak ' with Replace,norecoveryrestore DATABASE db from disk= ' d:\5.dif ' with Norecoveryrestore log db from disk= ' d:\6.trn ' with Norecoveryrestore LOG db from disk= ' d:\7.trn '--view s Elect * from db.dbo.test/*id1234567*/go--1.8.2restore DATABASE db from disk= ' D:\0.bak ' with Replace,norecoveryrestore LOG db from disk= ' d:\1.trn ' with NOR Ecoveryrestore log db from disk= ' d:\2.trn ' with Norecoveryrestore log db from disk= ' d:\4.trn ' with Norecoveryrestore LOG D b from disk= ' d:\6.trn ' with Norecoveryrestore LOG db from disk= ' d:\7.trn '--View select * FROM db.dbo.test/*id1234567*/

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

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