五餅二魚工作室

James Fu 的技術學習之路

2013-09-14

[SQL][Training]SQL 備份指令與觀念相關練習

[SQL][Training]SQL 備份指令與觀念相關練習

前一陣子指導一些夥伴準備微軟 70-462 的認證考試，因為沒有官方教材可以練習，因此自己準備了一些練習題目，讓這些夥伴練習一下。

 

先在磁碟機 E 建立本次課程的三個目錄，DB 目錄下存放資料庫，Script 目錄下存放課程的指令檔，Backup 目錄下存放備份檔案

image

 

建立一個測試資料庫，在本次練習當中，我們先設定該資料庫名稱為 Source，並且練習利用 「ALTER DATABASE」 的指令來變更資料庫的復原模式 ( 參考檔案 01_CreateDatbase.SQL )

-- 建立範例資料庫
CREATE DATABASE [Source]
 ON  PRIMARY 
( NAME = 'Source'    , FILENAME = 'E:\DB\Source.MDF' , SIZE = 40MB , FILEGROWTH = 10MB )
 LOG ON 
( NAME = 'Source_log', FILENAME = 'E:\DB\Source.LDF' , SIZE = 10MB , FILEGROWTH = 10MB )
GO

-- 設定 Recovery Mode 為簡單模式
ALTER DATABASE [Source] SET Recovery Simple
Go

-- 設定 Recovery Mode 為完整模式
ALTER DATABASE [Source] SET Recovery Full
Go

 

接著我們在這個測試的資料庫內放入一些資料，以便測試各種備份狀況下可以看出相關的差異 ( 參考檔案 02_CreateTable.SQL )

USE [Source]
GO

-- 關閉資料筆數的訊息
SET NOCOUNT ON

-- 建立範例資料表
CREATE TABLE [TestTable] ( [Field1] int )
GO

-- 建立 1000 筆測試資料
DECLARE @PTR INT
SET @PTR = 0
WHILE @PTR < 1000
BEGIN
  INSERT INTO [TestTable] VALUES ( @PTR )
  SET @PTR += 1
END

 

當資料表內有紀錄之後，我們可以開始測試各種備份指令了，首先會先使用完整備份，因為當使用差異備份和交易紀錄備份時，都必須使用完整備份來建立基準點，在這當我們也練習使用  「RESTORE VERIFYONLY」、「RESTORE FILELISTONLY」和「RESTORE HEADERONLY」的指令，這三個指令都不會真正去執行還原的動作，都只是用來檢查備份檔案，並且提供不同的資訊 ( 參考檔案 03_FullBackup.SQL )

USE [master]
GO

-- 完整備份資料庫
BACKUP DATABASE [Source] TO DISK = 'E:\Backup\Source_Full.BAK' WITH INIT
GO

-- 檢查備份檔是否 OK
RESTORE VERIFYONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
GO

-- 列出備份檔案內包含哪些資料庫檔案
RESTORE FILELISTONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
GO

-- 列出備份檔的備份資訊
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
GO

 

接著我們要開始練習做交易紀錄備份的練習，在這個練習當中，為了能看出這中間的差異，因此在每次備份當中，我們都會用指令去刪除一些資料，並且將所有的交易紀錄備份當放在同一個檔案當中，因此在練習過程中要注意，只有第一次的時候要使用「WITH INIT」的參數，而備份指令也改從原本的「BACKUP DATABASE」改成「BACKUP LOG」。( 參考檔案 04_LogBackup.SQL )

USE [Source]
GO

SET NOCOUNT ON
GO

-- 亂數刪除 10 筆資料 ( 0~200 之間 )
DECLARE @PTR INT
SET @PTR = 0
WHILE @PTR < 10
BEGIN
  DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 )
  SET @PTR += 1
END 

SELECT COUNT(*) FROM [TestTable]
GO

-- 第一個交易紀錄備份檔案
BACKUP LOG [Source] TO DISK = 'E:\Backup\Source_Log.BAK' WITH INIT
GO

-- 亂數刪除 10 筆資料 (200~400 之間 )
DECLARE @PTR INT
SET @PTR = 0
WHILE @PTR < 10
BEGIN
  DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 ) + 200
  SET @PTR += 1
END 

SELECT COUNT(*) FROM [TestTable]
GO

-- 第二個交易紀錄備份，不能加入 INIT 
BACKUP LOG [Source] TO DISK = 'E:\Backup\Source_Log.BAK' 
GO

-- 亂數刪除 10 筆資料 (400~600 之間 )
DECLARE @PTR INT
SET @PTR = 0
WHILE @PTR < 10
BEGIN
  DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 ) + 400
  SET @PTR += 1
END 

SELECT COUNT(*) FROM [TestTable]
GO

-- 第三個交易紀錄備份，不能加入 INIT 
BACKUP LOG [Source] TO DISK = 'E:\Backup\Source_Log.BAK' 
GO

 

接著我們用前面所介紹的「RESTORE HEADERONLY」的指令，來看一下完整備份和交易紀錄備份的檔案中，紀錄了哪些資訊以便讓 SQL Server 知道，當還原的時候依序要抓那些檔案，因此我們就會用以下的指令來查看備份檔案中的資訊。( 參考檔案 05_HeaderOnly.SQL )

-- 取得完整備份檔和交易紀錄備份檔內的備份資訊
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Log.BAK'
GO

 

在圖片的上方中呈現的完整備份的檔案資訊，下面有三個是交易紀錄備份的資訊，可以看出在備份檔案中會有紀錄備份資料是包含那些交易紀錄，這當中需要注意的是 LSN (記錄序號)，其中 FirstLSN 是表示備份組中第一個或最舊記錄的記錄序號，而 LastLSN 則是表示備份組之後下一個記錄的記錄序號。因此我們從交易紀錄備份檔中就會看到彼此之間的關聯。

image

 

接下來我們模擬在備份過程中，因為臨時需要一個完整備份資訊，但我們又不希望因此破壞原本的交易紀錄備份，因此在完整備份過程中，我們加入了「WITH COPY_ONLY」的參數，將資料庫備份到 Source_Copy.BAK 的檔案中。( 參考檔案 06_CopyOnly.SQL )

USE [Source]
GO
-- 完整備份配合 COPY_ONLY 參數
BACKUP DATABASE [Source] TO DISK='E:\Backup\Source_Copy.BAK' WITH INIT, COPY_ONLY
GO

 

接著我們又繼續做一些資料上的修改，並且產生差異備份檔 Source_Diff.BAK 的檔案。( 參考檔案 07_Differential.SQL )

USE [Source]
GO

-- 亂數刪除 10 筆資料 (600~800 之間 )
DECLARE @PTR INT
SET @PTR = 0
WHILE @PTR < 10
BEGIN
  DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 ) + 600
  SET @PTR += 1
END 

SELECT COUNT(*) FROM [TestTable]
GO

-- 第一個差異備份
BACKUP DATABASE [Source] TO DISK = 'E:\Backup\Source_Diff.BAK' WITH INIT, DIFFERENTIAL
GO

 

並且在差異備份之後，我們又繼續修改資料，此時產生第四個的交易紀錄備份。( 參考檔案 08_LogBackup.SQL )

USE [Source]
GO

-- 亂數刪除 10 筆資料 (800~1000 之間 )
DECLARE @PTR INT
SET @PTR = 0
WHILE @PTR < 10
BEGIN
  DELETE FROM [TestTable] WHERE [Field1] = ROUND( RAND()*200,0 ) + 800
  SET @PTR += 1
END 

SELECT COUNT(*) FROM [TestTable]
GO

-- 第四個交易紀錄備份，不能加入 INIT 
BACKUP LOG [Source] TO DISK = 'E:\Backup\Source_Log.BAK' 
GO

 

因此整個備份過程中，我們配合時間軸將備份過程給畫出來，會像是下圖中的呈現

image

 

因此我們再利用「RESTORE HEADERONLY」的指令，看一下備份檔案的資訊。( 參考檔案 09_HeaderOnly.SQL )

-- 取得完整備份檔、差異備份和交易紀錄備份檔內的備份資訊
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Full.BAK'
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_DIFF.BAK'
RESTORE HEADERONLY FROM DISK = 'E:\Backup\Source_Log.BAK'
GO

 

從下圖中就可以看出中間的差異備份的紀錄是根據完整備份的基準所建立的(紅色虛線)，而在最下面的交易紀錄並不受只複製備份和差異紀錄備份的影響，依然是一個接著一個的，而差異備份的 LastLSN 會在第四個交易紀錄，介於 FirstLSN 和 LastLSN 之間 ( 藍色虛線 )

image

 

因此如果當我們要還原資料庫的時候，可以使用兩種方式，一種是完整備份 + 四個交易紀錄備份，另外一種是完整備份 + 差異備份 + 交易紀錄備份，因此如果要使用第一種的話，則可以使用以下的指令來做還原，要注意除了最後一個還原指令是加上 「 WITH RECOVERY」，其他的都要加上「 WITH NORECOVERY」。( 參考檔案 11_RestoreWithLog.SQL )

-- 還原完整備份 ( 加入 NORECOVERY )
RESTORE DATABASE [Target] FROM  DISK = 'E:\Backup\Source_Full.BAK' WITH NORECOVERY, 
  MOVE 'Source'     TO 'E:\DB\Target.mdf',  
  MOVE 'Source_log' TO 'E:\DB\Target.ldf'
GO

-- 檢查資料庫的狀態
SELECT name,state_desc FROM sys.databases where name like 'Target%'
GO

-- 還原交易紀錄
RESTORE LOG [Target] FROM  DISK = 'E:\Backup\Source_Log.BAK' WITH  FILE = 1,  NORECOVERY
GO
RESTORE LOG [Target] FROM  DISK = 'E:\Backup\Source_Log.BAK' WITH  FILE = 2,  NORECOVERY
GO
RESTORE LOG [Target] FROM  DISK = 'E:\Backup\Source_Log.BAK' WITH  FILE = 3,  NORECOVERY
GO
RESTORE LOG [Target] FROM  DISK = 'E:\Backup\Source_Log.BAK' WITH  FILE = 4,  RECOVERY
GO

 

或者是使用三種備份方式合併使用的方式。( 參考檔案 12_RestoreWithDiff.SQL )

-- 還原完整備份 ( 加入 NORECOVERY )
RESTORE DATABASE [Target] FROM  DISK = 'E:\Backup\Source_Full.BAK' WITH NORECOVERY, 
  MOVE 'Source'     TO 'E:\DB\Target.mdf',  
  MOVE 'Source_log' TO 'E:\DB\Target.ldf'
GO

-- 檢查資料庫的狀態
SELECT name,state_desc FROM sys.databases where name like 'Target%'
GO

-- 還原差異備份+交易紀錄
RESTORE DATABASE [Target] FROM  DISK = 'E:\Backup\Source_Diff.BAK' WITH  NORECOVERY
GO
RESTORE LOG [Target] FROM  DISK = 'E:\Backup\Source_Log.BAK' WITH  FILE = 4,  RECOVERY
GO

 

一般我們在認證考試或者是相關書籍介紹備份指令的時候，多半都是說明採用三種備份方式合併使用，甚至一些人也誤以為當我們在使用差異備份之後，就一定要配合差異備份還原， 不能只使用交易紀錄備份還原了，因此希望藉由上述這些範例，可以讓大家對於備份和還原指令有些基本的認識，也可以透過這些指令來做一下練習，以備不時之需。

系列文章

標籤雲