Database Backup還原順序關係(環境:Microsoft SQL Server 2008 R2)

標籤：style   blog   io   os   使用   strong   for   檔案   資料   

讓新手們瞭解一下備份順序

--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 mastergo--建立測試CREATE DATABASE dbGOUSE dbGOCREATE TABLE Test(ID INT); --產生備份檔案 0.bakBACKUP DATABASE db TO DISK=‘d:\0.bak‘ WITH FORMATGO--1INSERT test SELECT 1go--產生備份檔案 1.trnBACKUP LOG db TO DISK=‘d:\1.trn‘ WITH FORMATgo--2INSERT test SELECT 2go--產生備份檔案 2.trnBACKUP LOG db TO DISK=‘d:\2.trn‘ WITH FORMATgo--3INSERT test SELECT 3go--產生備份檔案 3.difBACKUP DATABASE db TO DISK=‘d:\3.dif‘ WITH FORMAT,DIFFERENTIALgo--4INSERT test SELECT 4go--產生備份檔案 4.trnBACKUP LOG db TO DISK=‘d:\4.trn‘ WITH FORMAT--5INSERT test SELECT 5go--產生備份檔案 5.difBACKUP DATABASE db TO DISK=‘d:\5.dif‘ WITH FORMAT,DIFFERENTIAL--6INSERT test SELECT 6--產生備份檔案 6.trnBACKUP LOG db TO DISK=‘d:\6.trn‘ WITH FORMAT--7INSERT test SELECT 7--產生備份檔案 7.trnBACKUP LOG db TO DISK=‘d:\7.trn‘ WITH FORMATGO--SELECT * FROM dbo.Test/*ID1234567*/

2、還原順序

USE mastergo--1. 恢複時使用錯誤的日誌順序--1.1RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE;--查看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‘ --查看SELECT * FROM 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‘ --查看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‘--查看SELECT * FROM db.dbo.Test/*ID123*/go--1.5--1.5.1RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERYRESTORE DATABASE db FROM DISK=‘d:\3.dif‘ WITH NORECOVERYRESTORE LOG db FROM DISK=‘d:\4.trn‘--查看SELECT * FROM db.dbo.Test/*ID1234*/GO--1.5.2RESTORE DATABASE db FROM 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‘--查看SELECT * FROM db.dbo.Test/*ID1234*/go--1.6RESTORE DATABASE db FROM DISK=‘d:\0.bak‘ WITH REPLACE,NORECOVERYRESTORE DATABASE db FROM DISK=‘d:\5.dif‘ --查看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 NORECOVERYRESTORE LOG db FROM DISK=‘d:\6.trn‘ --查看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 NORECOVERYRESTORE LOG db FROM DISK=‘d:\4.trn‘ WITH NORECOVERYRESTORE LOG db FROM DISK=‘d:\6.trn‘ --查看SELECT * FROM db.dbo.Test/*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‘--查看SELECT * 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 NORECOVERYRESTORE LOG db FROM DISK=‘d:\2.trn‘ WITH NORECOVERYRESTORE LOG db FROM DISK=‘d:\4.trn‘ WITH NORECOVERYRESTORE LOG db FROM DISK=‘d:\6.trn‘ WITH NORECOVERYRESTORE LOG db FROM DISK=‘d:\7.trn‘--查看SELECT * FROM db.dbo.Test/*ID1234567*/

Database Backup還原順序關係(環境:Microsoft SQL Server 2008 R2)