201607080900SQL Server 資料庫的備份與還原

資料庫備份策略:  

  • 每天執行一次完整備份 ( AM 3:00 )
  •  小執行一次交易記錄備份  

***只有交易檔備份可以截斷,以免記錄檔 (LDF) 不斷的成長。

完整備份還原 –> 交易記錄備份還原  

  1. 先還原完整備份
  2. 再還原完整備份後所做的交易記錄備份  

***交易檔 (LSN) 的啟始於你的第一個完整或差異備份。

==================================================

http://blog.miniasp.com/post/2010/04/21/SQL-Server-Full-Differential-Transaction-Backup.aspx

 

觀念釐清:SQL Server 完整備份、差異備份、交易記錄備份

 

SQL Server 完整備份會截斷交易記錄,其實不然

完整備份 

  • 同時會備份 資料檔 ( *.mdf ) 與 交易記錄檔 ( *.ldf )
  • 要做任何差異備份交易記錄備份之前,一定要做一次完整備份

差異備份

  • 只會備份 資料檔 ( *.mdf ),但僅有最近一次完整備份備份當下這段時間所異動的資料
  • 差異備份不會備份任何交易記錄檔,備份的內容只會拿目前資料檔最近一次完整備份的資料內容做差異比對

交易記錄檔備份

  • 只會備份 交易記錄檔 ( *.ldf )
  • 若透過 SSMS 操作,在預設的情況下會自動截斷交易記錄
  • 交易記錄備份的 紀錄序號 (LSN) 是從資料庫建立開始就一直連貫的

如何證明上述觀念正確?

透過查詢 msdb 的 backupset 系統資料表可以查詢出所有 備份組 (backupset) 的備份歷史紀錄,請參考以下 T-SQL 語法: 

SELECT 
	a.[database_name] as '資料庫名稱',
	CASE a.[type]
	WHEN 'D' THEN N'資料庫'
	WHEN 'I' THEN N'差異資料庫'
	WHEN 'L' THEN N'紀錄'
	WHEN 'F' THEN N'檔案或檔案群組'
	WHEN 'G' THEN N'差異檔案'
	WHEN 'P' THEN N'部分'
	WHEN 'Q' THEN N'差異部分'
	ELSE N'NULL'
	END as '備份類型',
	a.[first_lsn] as '備份組中第一個LSN',
	a.[last_lsn]  as '備份組之後下一個LSN',
	a.[database_backup_lsn] as '最近的完整資料庫備份之LSN',
	a.[differential_base_lsn] as '差異備份的基底 LSN',
	a.[backup_finish_date] as '備份完成的日期和時間'
FROM
	msdb..backupset a INNER JOIN master..sysdatabases b ON 
	a.database_name COLLATE DATABASE_DEFAULT 
       = b.name COLLATE DATABASE_DEFAULT 
ORDER BY
	a.database_name, a.backup_finish_date

透過下圖執行結果 (點圖可放大),很容易能理解備份時交易記錄的連貫特性:

第一次交易記錄備份時的備份組中第一個或最舊的紀錄序號(LSN)是一致的,代表交易記錄備份是從整個資料庫的「第一筆交易資料開始備份」:

接著我們再看看該資料庫所有交易紀錄備份的中 LSN 完全是連貫的,所以完整備份差異備份都不會影響交易記錄備份的 LSN 連慣性:

 資料庫如何依序還原?

基本上 SQL Server 有能力將資料庫的資料還原到任何一個資料庫備份的時間點,假設你每一分鐘備份一次交易記錄的話,若你的資料庫發生毀損,透過這些備份資料還原最多只會遺失 1 分鐘以內的資料,如果你想還原到 3 天前的特定時間,也可以透過交易記錄備份還原到特定的備份時間點。

由於交易記錄備份的 LSN 不會中斷的關係,你甚至可以從第一次完整備份開始逐一將交易記錄還原到任意時間點 (使用 RESTORE 可以搭配 STOPAT | STOPATMARK | STOPBEFOREMARK 等參數來還原),但若資料庫存取量非常大或資料庫已運行多年,可能會導致交易記錄備份檔非常佔據硬碟空間,從實務上來講通常不會保留這麼完整的交易記錄備份,但對於資料完整性非常要求的單位(如:金融業)就有可能被要求要保留完整的交易記錄資料。

為了加快資料還原的速度,我們通常不會從第一次完整備份開始逐一將交易記錄備份檔還原,而會透過最近一次的完整備份檔來還原資料庫,甚或透過完整備份與差異備份的機制縮短交易記錄備份還原的時間,一般來說有以下幾種資料還原策略:

完整備份還原 –> 交易記錄備份還原 

  1. 先還原完整備份
  2. 再還原完整備份後所做的交易記錄備份 

***交易檔 (LSN) 的啟始於你的第一個完整或差異備份。

完整備份還原 –> 差異備份還原 –> 交易記錄備份還原 

  1. 先還原完整備份
  2. 再還原差異備份
  3. 再還原差異備份後所做的交易記錄備份 

備註:只要交易記錄備份的紀錄序號能夠連貫,SQL Server 就可以透過交易記錄備份還原資料。

以下是一個實務上常用的資料庫備份策略: 

  • 每月第一天執行一次完整備份 ( AM 3:00 )
  • 每個星期天執行一次差異備份 ( AM 4:00 )
  • 每 15 分鐘執行一次交易記錄備份 

假設你想將資料庫還原到 2010/4/21 PM 9:00 的版本,你就必須會用以下順序還原資料庫: 

  • 利用 2010/4/1 AM 3:00 的完整備份還原資料庫
  • 再還原 2010/4/18 (日) AM 4:00 的差異資料備份
  • 再還原所有 2010/4/18 (日) AM 4:00 之後做的的交易紀錄備份

 

還原時的注意事項:

還原的過程中在最後一個備份檔還原之前都必須保持 不回復為認可的交易 (NORECOVERY) 的狀態:

 

關於查詢所有 備份組 (backupset) 的備份歷史紀錄,以下是較完整的 T-SQL 語法,相關欄位可以參考《Microsoft SQL Server 線上叢書》對於 backupset (Transact-SQL) 的說明:

 

SELECT 
	a.[database_name] as '資料庫名稱',
	CASE a.[type]
	WHEN 'D' THEN N'資料庫'
	WHEN 'I' THEN N'差異資料庫'
	WHEN 'L' THEN N'紀錄'
	WHEN 'F' THEN N'檔案或檔案群組'
	WHEN 'G' THEN N'差異檔案'
	WHEN 'P' THEN N'部分'
	WHEN 'Q' THEN N'差異部分'
	ELSE N'NULL'
	END as '備份類型',
	a.[name] as '備份組的名稱',
	a.[first_lsn] as '備份組中第一個或最舊的記錄序號',
	a.[last_lsn]  as '備份組之後下一個記錄的記錄序號',
	a.[database_backup_lsn] as '最近的完整資料庫備份之記錄序號',
	a.[differential_base_lsn] as '差異備份的基底 LSN',
	a.[backup_finish_date] as '備份作業完成的日期和時間',
	a.[backup_size] as '備份組的大小 (以位元組為單位)'

FROM
	msdb..backupset a INNER JOIN master..sysdatabases b ON 
		a.database_name COLLATE DATABASE_DEFAULT = 
		b.name COLLATE DATABASE_DEFAULT 
ORDER BY
	a.database_name, a.backup_finish_date

 

 

心得分享

其實我對 IT 工作一直有個體悟,由於我們不可能看完所有 MSDN 或 TechNet 文件,看過也不一定會記得,所以有些工作就算你真的在實務上知道怎麼做也不見得自己理解的觀念是對的,所以保持著一顆虛懷若谷的心還真的蠻重要的,不過該有自信的時候千萬不要畏縮!

像在我們公司的工作環境裡隨時充滿著挑戰,每個人隨時都可以挑戰你的觀念,連老闆也不例外,挑戰成功可能還有意外驚喜,例如有免費中餐之類的 XD,如果有邏輯說不通的、理論與實務不符的地方、沒效率的作法、無法清楚表達的抽象概念、……等等,雖時都可以提出自己的看法與意見,為了追求技術的真理我們毫不手軟,所以在公司內部我也經常與同事討論、甚至是辯論一些大家看法不太一致或不太確定的地方,為的就是讓每個人對於特定知識的抽象概念能有一致且正確的理解,另一方面也是加強初學者的自信心,因為無法理解抽象概念的人講話永遠比較小聲,當理解前因後果來龍去脈之後就會慢慢展現自信,相對的工作出錯的機率也會降低!

相關連結

 

====================================================

 http://caryhsu.blogspot.tw/2012/02/blog-post_21.html

 

備份與還原觀念介紹與策略規畫

 

 

備份與還原觀念介紹與策略規畫

        資料庫的備份工作非常的重要,是每一位 DBA 首要工作,這也是許多的首次接任 DBA 面對的問題之一,反正不管是面對什麼不同的資料庫,反正就是先備份就對了,因為當問題發生時,只要還原你的備份即可,但是隨著資料庫越來越大的時候,備份的時間會越來越長,而且備份的過程中,因為有大量的 I/O ,所以反而造成資料庫的問題。

SQL Server主要分成二種檔案類型,一個是資料檔 (MDF or NDF),而另一個是交易紀錄檔(LDF),我常遇到的一個情況,那就是資料檔可能不到1G,但是交易紀錄檔已經成長到10G左右,最後造成磁碟空間不足,這個也是因為備份之後沒有截斷交易紀錄檔的關係,解決方法,我們稍後再來說明。

上述的問題主要都是在備份方式的選擇,所以現在我們來介紹如何規畫備份與還原計畫,而在介紹之前,我們先來說明一下資料庫的復原模式。復原模式簡單的說,就是當你的資料庫毀損時,你希望可以還原到特定的時間點藉以減少資料損失。

完整備份是最好的備份方案,但是相對的在每次有 Insert、Update、Delete的時候,都需要進行交易紀錄檔的抄寫,所以也會影響到效能的部份,如果你有需要一次 Import 大量的資料時,你可以將復原模式先切換成 [大量記錄] ,如果就可以加快 Import 的交易速度,通常復原模式的選擇,取決與資料異動的頻率,如果某些資料庫上的資料很少異動,其實你就可以選擇簡單模式即可。

復原
模式
描述 工作損失風險 復原至時間點
簡單 無記錄備份。
自動收回記錄空間,使空間需求保持在最低,實際消弭管理交易記錄空間的需求。
最近一次備份之後所做的變更並未受到保護。如果發生損毀事件,則必須重做這些變更。 只能復原至備份結束時。
完整 需要記錄備份。
不因損失或損毀資料檔而失去任何工作。可復原至任意時間點(例如,應用程式或使用者錯誤前)。
通常沒有。
如果記錄結尾損毀,必須重做最近一次記錄備份後的變更。
可以復原至特定時間點(假設您已完成至該時間點的備份)。
大量記錄 需要記錄備份。
完整復原模式的輔助,允許執行高效能的大量複製作業。針對大多數的大量作業使用最少記錄,以減少記錄空間的使用量。
如果記錄損毀,或在最近一次記錄備份後進行過大量記錄作業的話,必須重做最近一次備份後的變更。否則不會損失任何工作。 可復原至任何備份結束時。不支援時間點復原。

在來我們來介紹一下備份的種類,在SQL Server上總共提供五種備份方式(如下表),最常用的是前三種,請參考下列說明。
備份類型描述
完整 整個資料庫的完整備份。
差異 這個備份僅包含每個檔案自最近資料庫備份後修改過的資料範圍。
交易檔 每個記錄備份都會涵蓋建立備份當時正在進行中的交易記錄部分,而且也包含上一次記錄備份未備份到的所有記錄。
檔案 / 檔案群組 這是一或多個檔案或檔案群組中所有資料的完整備份。
差異檔案 這是一或多個檔案的備份,其中包含自從每個檔案最近完整備份後變更過的資料範圍。


再來另一個使用復原模式為完整時常遇的到問題,那就是交易檔(LDF)過大,常常造成磁碟空間不足的問題,因為交易檔本身只有當你進行交易檔備份的時候才會清除,其中的完整與差異都不會,為了證明這個情況,我作了以下的實驗。


交易檔的連結,本身是透過 LSN 進行連結,如下圖所示。

底下我總共作了13次的備份,分別為 差異 -> 紀錄 -> 完整 -> 差異 -> 紀錄 -> 差異 -> 紀錄 ->完整 -> 差異 -> 紀錄 -> 完整 -> 完整 -> 紀錄,說明如下:

備份測試:
1、交易檔 (LSN) 的啟始於你的第一個完整或差異備份。
2、從第二次的交易檔備份到第五次的交易檔備份,中間雖然有一次完整與差異備份,但是都只是包含交易檔的部份,但並沒有截斷交易檔,所以這證明了只有交易檔備份可以截斷,以免記錄檔 (LDF) 不斷的成長。

 

另一種方式,您也可以透過 DBCC Log(DBName) 的指令查詢目前記錄檔 (LDF) 的筆數,當您進行交易檔備份後,你就會發生回傳的筆數就會變少了。



最後我們來看一下備份與復原模式的相關,請參考下圖說明。
Recovery Model/ BackupCompleteDifferentialTransaction LogFile / Filegroup
Simple Required Allowed Not Allowed Not Allowed
Bulk-Logged Required Allowed Required Allowed
Full Required Allowed Required Allowed


備份策略範例:
我們通常透過完整 + 差異 + 紀錄來規畫資料庫的備份策略,但是怎樣的方法才是最好的,其實沒有一定的公式,取決於備份時的時間、資料可能遺失時間長度、還原時的時間等因素,下列我透過 MSDN 上範例提供給大家參考,希望大家可以透過這個範例學習後,調整出最適合資料庫的備份策略。

資料描述:
Database /
Parameter
Sales/Customer
Size
3.5GB
Usage
Track customer orders and shipments
Activity Pattern
Most heavily used during weekdays. Customer orders are added during business hours. Reports are prepared at nights.
Disaster Recovery Requirements
High usage and visibility database. Critical to company operations. Require point-of-failure recovery. This system should be operational within 20-30 minutes if outage happens during working hours. No data loss is acceptable.

首先將資料庫的復原模式設定為 Full,如此可以讓系統保持最大還原到特定時間點的可能性,並且保持每天 PM 10:00進行完整備份、AM 11:00與 PM 4:00進行差異備份,每10分鐘進行一次交易備份。

在這樣的模式,最差的情況下,可以會損失約10分鐘的資料,但這種的情況非常的低,因為這是當你的LDF檔案完全不能讀的情況下才會發生,假設故障發生在星期一的AM 11:21 分,這時候在新的機器上,你就需要先還原星期天的完整備份,然後再還原星期一 AM 11:00的差異備份,最後再依序還原 AM 11:10、AM 11:20的交易備份,如果你想還原到特定的時間點,如 AM 11:16分,在進行最後一個交易備份時,配合 STOPAT 的備份參數即可。


參考連結:
SQL Server 2000 Backup and Restore
http://technet.microsoft.com/en-us/library/cc966495.aspx#EBAA
Designing a Backup and Restore Strategy
http://msdn.microsoft.com/library/aa173660.aspx
Analyzing Availability and Recovery Requirements
http://msdn.microsoft.com/zh-tw/library/aa196617.aspx
Planning for Disaster Recovery
http://msdn.microsoft.com/zh-tw/library/aa196629.aspx
記錄序號和還原計畫
http://msdn.microsoft.com/zh-tw/library/ms190729.aspx
backupset
http://msdn.microsoft.com/en-us/library/ms186299.aspx
備份概觀 (SQL Server)
http://msdn.microsoft.com/zh-tw/library/ms175477.aspx
復原模式概觀
http://msdn.microsoft.com/zh-tw/library/ms189275.aspx
如何:還原到某個時間點 (Transact-SQL) 
http://msdn.microsoft.com/zh-tw/library/ms179451.aspx

關鍵字:SQL ServerBackupRestoreRecoveryRecovery Model備份還原復原模式

 

 

 

 

https://msdn.microsoft.com/zh-tw/library/ms187048(v=sql.120).aspx

SQL Server 資料庫的備份與還原

此主題描述備份 SQL Server 資料庫的優點、基本備份和還原詞彙,並介紹 SQL Server 的備份和還原策略,以及 SQL Server 備份和還原的安全性考量。

SQL Server 的備份和還原元件提供基本的防護措施,可保護 SQL Server 資料庫中所儲存的重要資料。 若要將重大資料遺失的風險降到最低,則需要定期備份資料庫,以保留對資料的修改。 計畫完善的備份和還原策略,可協助保護資料庫免於因各種失敗造成損毀而遺失資料。 藉由還原備份組再復原資料庫,以測試您的策略,讓您準備好有效因應損毀情況。

除了儲存備份的本機儲存體之外,SQL Server 也支援備份至與還原自 Windows Azure Blob 儲存體服務。 如需詳細資訊,請參閱 SQL Server 備份及還原與 Windows Azure Blob 儲存體服務

回應
關鍵字





Powered by Xuite
    沒有新回應!