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

Source: Internet
Author: User
Tags filegroup

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)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.