SQL Server 資料庫備份還原和資料恢復

pursuer.chen發表於2014-04-02

SQLServer資料庫資料恢復

認識資料庫備份和事務日誌備份

資料庫備份與日誌備份是資料庫維護的日常工作，備份的目的是在於當資料庫出現故障或者遭到破壞時可以根據備份的資料庫及事務日誌檔案還原到最近的時間點將損失降到最低點。

資料庫備份

資料庫備份可以手動備份和語句備份

一.手動備份資料庫

1.滑鼠右鍵選擇你要進行備份的資料庫-任務-備份

可以在常規選項頁面你可以選擇備份型別是進行完整資料庫備份還是差異資料庫備份

2.點選新增選項，選擇資料庫檔案的存放路徑

注意檔名記得加字尾.bak,便於恢復時的查詢

3.你還可以在選項頁面是追加到現有的備份集，還是覆蓋所有的現有備份集，還可以選擇備份驗證完整性（建議選擇），還可以選擇是否壓縮備份等。

二.語句備份資料庫

use master 
go
BACKUP DATABASE [test] TO  DISK = N'D:\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\test.bak' WITH NOFORMAT, NOINIT,  NAME = N'test-完整 資料庫 備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

資料庫日誌備份

首先需要注意，資料庫日誌的備份是基於資料庫完整備份，也就是說你備份資料庫日誌之前你首先要先對資料庫進行一次完整的備份，因為之間會涉及到堅持到檢查點lsn，這也是本文接下來要講的重點。

一.手動備份資料庫日誌

1.右鍵資料庫-任務-備份-選擇備份型別（事務日誌）

2.點新增，新增日誌檔案備份儲存路徑

3.同資料庫完整備份一樣，你也可以選擇覆蓋現有備份集或者追加到現有備份集，這裡現在覆蓋現有備份集、驗證完整性，然後確認備份

二.語句備份資料庫事務日誌

BACKUP LOG [test] TO  DISK = N'D:\test.trn' WITH NOFORMAT, INIT,  NAME = N'test-事務日誌  備份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

資料庫還原

右鍵資料庫-還原資料庫-新增需要進行還原的資料庫檔案路徑

在還原源選項中你可以選擇‘源資料庫’，‘源裝置’。1.選擇源資料庫工具會自動顯示該資料庫之前的一些備份，然後直接選擇需要還原的資料庫備份集。

2.選擇源裝置點選後面的...，新增需要還原的資料庫檔案

2.點選確認還原資料庫

資料庫恢復

資料庫恢復的前提是1.一個完整的資料庫備份2.包含這個完整資料庫備份的事務日誌備份3.完整備份之間也可以存在數個差異備份

對於資料庫維護空間始終是一個比較頭疼的問題，特別是對於大型資料庫而言，每天的日誌檔案增長是龐大的，很多資料庫管理員會定時對資料庫日誌檔案進行收縮，但是經常收縮會存在收縮完日誌檔案還是不能減少，這是因為存在很多活動的日誌無法收縮可以用

DBCC LOGINFO('
資料庫名稱
')   
 
我們看到
status=0
的日誌，代表已經備份到磁碟的日誌檔案；而
status=2
的日誌還沒有備份。當我們收縮日誌檔案時，收縮掉的空
間其實就是
status=0
的空間，如果日誌物理檔案無法減小，這裡一
定能看到非常多
status=2
的記錄

解決辦法：1.可以分離要收縮的資料庫，然後手動刪除日誌檔案，然後附加資料庫，資料庫就會產生一個很小的日誌檔案（不推薦使用這種方法）

2.右鍵要出來的資料庫選擇“屬性”-"選項"，將恢復模式改成"簡單"，然後利用收縮工具可以講日誌檔案收縮到很小，收縮完記得講恢復模式改成"完整"

也可以用語句進行處理(dbname是你要進行收縮的資料庫名，dbname_log是你要進行收縮的資料庫的邏輯日誌名稱)

USE [master]
    GO
    ALTER DATABASE [dbname] SET RECOVERY SIMPLE WITH NO_WAIT
    GO
    ALTER DATABASE [dbname] SET RECOVERY SIMPLE   --簡單模式
    GO
    USE [dbname]
    GO
    DBCC SHRINKFILE (N'dbname_log' , 11, TRUNCATEONLY)
    GO
    USE [master]
    GO
    ALTER DATABASE [dbname] SET RECOVERY FULL WITH NO_WAIT
    ALTER DATABASE [dbname] SET RECOVERY FULL

對於第一種方法不贊同使用，首先對於資料庫的分離與附加有時候會破壞資料庫，造成資料庫無法還原，還有就是對於線上資料庫也不允許進行分離操作。

對於第二種方法是slq2008收縮日誌檔案的一種方法，但是此方法也不能使用過於頻繁，因為進行資料庫恢復模式的更改會截斷事務日誌檔案，這樣的話當時利用事務日誌檔案進行恢復的時候檢查點不能包含資料庫檔案，而且當你要對事務日誌進行備份的時候會重新提示你需要對資料庫進行完整備份。

舉個例子：比如你昨天晚上進行了一次完整備份，然後同時你也進行了一次日誌備份，然後你每個小時進行過一次差異備份，最近的差異備份時間點是14點，如果此時資料庫錯誤修改了資料，你可以立馬備份一個日誌檔案將資料庫恢復到日誌備份開始到日誌備份終點前的任意時間點。

如果此時你進行了修改資料庫模式，截斷日誌進行了收縮，那麼你的資料只能恢復到昨天晚上備份的那個日誌備份時間前的任意時間點，也就是今天所做的資料庫更改無法再恢復了，因為日誌檔案已經被截斷了.

因為日誌檔案的檢查點（lsn）是連續的，每一次日誌備份都是在上一次備份的基礎上lsn往後增加的，lsn的範圍也包括了資料庫檔案的lsn，也只有日誌檔案的lsn包括了資料庫檔案的lsn,才能將資料庫檔案進行回滾。

總結

備份還原看似簡單，而且現在的圖形化的工具更加讓人對備份還原的理解不夠深入，特別的日誌備份如果你不仔細研究一下會存在很多誤區。

1.完整備份和差異備份的BAK中也會備份日誌檔案，在備份的時候生成檢查點但是該檢查點只是標識（好比將日誌檔案進行歸檔，當我們查詢fn_dblog的時候發現日誌少了，但實際上日誌檔案還是存在的，當你這時候備份日誌的時候你依然會備份到那部分歸檔掉的日誌，但是備份日誌的時候生成檢查點checkpoin就會將日誌進行截斷，將不活動的那部分日誌清空）作用並不截斷日誌。

2.當日志進行第一次備份的時候是自最近一次完整備份之後的日誌進行備份，當下次再進行日誌備份（前提日誌未被截斷）的時候是備份上一次日誌備份的last_lsn之後到當前備份之間的日誌記錄，不管中間是否存在完整或者差異備份都不會減少日誌的量，不要誤理解為當上一次備份日誌之後中間存在完整備份然後再備份日誌備份的日誌記錄就是完整備份之後的記錄。

3.第一次的日誌備份的first_lsn一定是和日誌備份之前的完整備份的first_lsn相同，如果備份集最後一次備份是日誌備份那麼最後一次日誌備份的last_lsn一定包含自第一次完整備份以來所有的完整好差異備份的last_lsn。所有可以通過第一次完整備份和第一個日誌備份和最後一個日誌備份（中間沒有日誌備份）還原資料庫到第一次完整備份之後的任何一個時間點，無論中間是否存在完整差異備份。

4.為什麼備份集之間需要完整或者差異備份，如果沒有完整或者差異備份如果自第一次備份以來很長時間沒有進行完整或者差異備份那麼當進行故障還原的時候需要耗費很長的時間，當中間如果存在差異備份的時候那麼還原日誌的時間將大大減少，不需要在執行完整到差異這部分的redo/undo。

5.由於SQLSERVER是日誌先寫的機制，所以當資料庫出現故障也是資料出現了故障一般都是進行了寫日誌操作但是在寫資料的時候出現了什麼問題，所以當進行日誌的還原操作的時候會進行這些檢查，保證事務的一致性這也是為什麼DBCC CHECKDB檢查資料庫錯誤一般還原是最好的方法，當你將日誌進行還原的時候所有的日誌備份的操作都會重新進行操作一次。

6.差異備份的原理，差異備份與日誌備份不一樣，日誌備份是每次的備份都是自上一次備份以來的增量，差異備份是自上一次完整備份以來的增量，所以無論中間有多少次差異備份，都只需要還原完整備份與最後一次差異備份即可。

select name,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,backup_start_date ,backup_finish_date 
 from msdb..backupset where database_name=N'InsideTSQL2008' 
 
SELECT * FROM [sys].[fn_dblog](NULL,NULL) 

SELECT  *
FROM    fn_dump_dblog(NULL, NULL, N'DISK', 1,N'd:\InsideTSQL2008.bak', DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT)