SQLServer2008備份和恢復

目標：
1.理解SQLServer2008R2的備份方式(完整，差異，日志備份)和恢復模式;
2.能夠進行完整備份，差異備份操作,事務日志備份，并進行恢復操作;
3.使用維護計劃實現日常的數據庫備份操作;

一、SQLServer2008R2的備份恢復

1、恢復模式類型

所有的數據庫都可以設置為三個不同的恢復模式：簡單(simple)， 完全(full)，大容量日志(Bulk-Logged).

A完全恢復模式
完全恢復模式是默認的恢復模式。在完全恢復模式下，需要手工的對事務日志進行管理。使用完全恢復模式的優點是可以恢復到數據庫失敗或者指定的時間點上。缺點 則是，如果沒有進行管理的話，事務日志將會快速增長，消耗磁盤空間。要清除事務日志，只能通過備份事務日志，或者切換至簡單模式。
B簡單恢復模式
與完全恢復模式不同的是，在簡單恢復模式下，在檢查點發生時（checkpoint），當前已被提交的事務日志將會被清除。
因此，在簡單恢復模式下，容易造成數據丟失，因為無法將數據庫恢復到失敗的那一刻。需要注意的是，雖然在簡單恢復模式下，系統會自動定期清除日志，但這并不意味著事務日志文件不會增長。例如，如果執行一個批量插入操作時，SQL SERVER會將該相關操作當成一個事務，期間產生的日志量在極端情況下，還是非常可觀的。

C大容量日志恢復模式

大容量日志恢復模式與完全恢復模式非常相似，但與完全恢復模式不同的是，批量操作將會盡量被最少記錄。

批量操作有以下幾種類型：

1. 批量導入數據，例如使用BCP(Bulk Copy Import) ， BULK INSERT命令 ，或者是在BULK使用OPENROWSET命令；
2. 大對象操作(LOB),例如在TEXT, NTEXT, IMAGE 列上使用WRITETEXT 或者UPDATETEXT；
3. SELECT INTO 字句；
4. CREATE INDEX, ALTER INDEX, ALTER INDEX REBUILD, DBCC REINDEX
   在完全恢復模式下，上述操作產生的日志將會是非常大的。而使用大容量日志恢復模式將會阻止不需要或者非預期的日志增長。
   在批量操作發生時，SQL SERVER僅僅記錄了相關數據頁(data page)的ID， 在SQL SERVER中，SQL SERVER pages 都有內部 ID，如5：547。用這種方式，能夠將大量的page ID記錄在小的日志文件里。
   使用大容量日志恢復模式，將會使數據倉庫或者有大批量操作的數據庫減少很大的空間。但使用大容量恢復模式時，會使得恢復變得比較困難，一般來說，只能恢復到最后的事務日志備份點上，但如果所有的事務日志都被備份后，還是可以恢復成功的。
   2、改變恢復模式
   改變數據庫的恢復模式，可以通過以下語句來實現：
   ALTER DATABASE database_name SET RECOVEY BULK_LOGGED
   改變恢復模式并不需要重啟數據庫實例。
   二、數據庫備份介紹
   1、備份位置
   在SQL Server上，有多種備份位置可以選擇，如本地磁盤，網絡磁盤，遠程地址，磁帶等。各種備份位置均有自己的優點和缺點。
   2、邏輯備份設備
   在SQL Server上，可以通過創建邏輯備份設備來完成備份。使用邏輯備份設備的好處是，當變更備份地址時，不需要更改備份腳本，只需要更改邏輯備份設備的定義即可。

創建邏輯備份設備的腳本如下：?
SQL code
EXEC sp_adddumpdevice @devtype=’disk’,@logicalname=’MYBackup’,@physicalname=’D:\backup\mydb.bak’
刪除備份設備的腳本：?
SQL code
Sp_dropdevice @logicalname=’MYBackup’
上述腳本只是刪除邏輯備份設備的定義，下述腳本將同時刪除備份文件：?
SQL code
Sp_dropdevice @logicalname=’MYBackup’,@devfile=’DELFILE’

使用邏輯備份設備的方法如下：?
SQL code
Backup database mydb to MYBackup
當然，還可在邏輯備份設備上指定過期時間等備份屬性，如：?
SQL code
Backup database mydb to MYBackup WITH EXPIREDATE=’13/01/2010’
或：?
SQL code
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7

3、備份集與存儲集

每一份備份包含于一個備份集，而一個備份集包含于一個存儲集。通過系統GUI進行備份時，SQL Server會自動指定備份集和存儲集，目的則是為了簡化管理。用T-SQL顯示指定則用如下語法：
SQL code
BACKUP DATABASE mydb to MYBackup WITH RETAINDAYS=7,
NAME=’FULL’,MEDIANAME=’ALLBackups’
NAMEs是指備份集名稱，MEDIANAME是指存儲集名稱。

4、全備份
不管恢復模式是哪一個，所有的備份都必須要有一個全備份，特別是日志備份和差異備份，如果沒有全備份的話，將無法進行恢復。
簡單的全備份腳本如下所示，也可以通過維護計劃來指定全備份：?
SQL code
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’
但需要注意的是，上述命令是將數據庫備份附加到當前的存在的文件上，如果不存在則創建它，并不會覆蓋原有文件。要覆蓋同名的備份文件，需要指定INIT參數。?
SQL code
BACKUP DATABASE mydb to DISK=’D:\Backup\mydb.bak’ WITH INIT

5、日志備份
在完全恢復模式或者大容量日志恢復模式下，日志備份不僅僅是恢復的需要，同時也是手工管理事務日志文件的一種方式。如果從不進行備份的話，在完全恢復模式或者大容量恢復模式下，事務日志將會持續增長，直至消耗完所在磁盤。
日志備份的腳本如下：?
SQL code
BACKUP LOG mydb_log TO DISK=’D:\backup\mydb.trn’
需要養成使用.trn為日志備份的擴展名的習慣。

每個在數據庫上的動作都會被安排一個Log Sequence Number (LSN)。如果需要還原到指定的時間點，需要有持續的LSN記錄。也就是說，在完全恢復模式或者大容量日志模式下，一個不被打斷的事務日志備份鏈是恢復數據庫的基本要求。

6、差異備份
使用日志備份來恢復時，無疑是一個很慢的過程，特別是上一個全備份的歷史比較悠久時。使用差異備份，便能縮短恢復時間。事實上，差異備份只是BACKUP DATABASE的一個選項，如下：?
SQL code
BACKUP DATABASE mydb TO DISK=’D:\backup\mydb.dif’ WITH DIFFERENTIAL,INIT

進行數據庫恢復時，先恢復數據庫全備份，再恢復數據庫差異備份，最后才恢復日志備份。差異備份是與上一次全備份緊密相連的，不管期間有多少次日志備份和差異備份，差異備份還是會從上一次全備開始備份。因此，經常會遇到這樣的一種情況，在生產 庫上需要臨時使用數據庫時，便用BACKUP DATABASE … TO DISK=’..’進行了一個備份，下一次的差異備份便會以這回的全備為準，如果過后把這個臨時全備刪除掉后，后面的差異備份就沒用了。差異備份并不意味著磁盤空間肯定會少，這取決于實際情況。當期間大量操作發生時，差異備份還是會變得很大。
7、錯誤檢測

在備份過程中，備份進程會同時驗證數據，或者校驗不完整頁（torn page），或者驗證校驗和(checksum)。要使用該功能，需要激活該選項。
不完整頁檢測(Torn-page dection)僅僅檢查每一個頁看是否已經寫完成。如果發現一個頁只有部分被寫入，那么就將其標記為torn。
校驗和驗證(checksum validation)是一種新的頁驗證機制。它會為每個頁添加一個值來表明該頁實際的大小。雖然看起來是個代價很高影響性能的操作，但事實上，它的效率非常高，與torn-page差不多。
備份進程在備份數據庫時，會通過比較在數據庫里的和隨著備份頁寫入硬盤時這兩個之間的值來進行驗證。但是，這個驗證并不是自動完成的，需要顯示指定，在GUI頁面上是個選項。如果通過T-SQL來備份的話，語句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\data\mydb.bak’ WITH CHECKSUM

如果備份過程中，發現了錯誤，SQL Server會錯誤信息寫入MSDB上的SUSPECT_PAGE表里面。同時，在默認情況下，備份行為會停止的（STOP_ON_ERROR），以便管理員排查錯誤。
但備份過程中的校驗和驗證還有另外一個選項（CONTINUE_ON_ERROR）,也就是說，如果發現錯誤，備份過程并不會中斷，而是將錯誤頁信息記錄在 MSDB..SUSPECT_PAGE上而已。需要注意的是，SUSPECT_PAGE表是有行限制的，最多只能達到1000行，如果達到了的話，備份同 樣會失敗。激活校驗和驗證的話，很明顯會影響備份的性能。但還是很有必要的。

8、安全備份
完全備份和日志備份語句還支持使用密碼屬性，如：?
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’ WITH PASSWORD=’mydb’

所指定的密碼是很容易破解的。因此，如果確實需要對某些備份數據進行加密的話，可以將備份存放于加密的文件系統或者其它安全的存儲設備上。
同時，SQL Server還提供了對真實列進行加密的功能。該加密功能是工業標準。
9、條帶備份
有些情況下，單獨一個硬盤無法存儲一個完整的數據庫備份時，可以將數據庫備份分成多個部分存儲在不同的磁盤上，這種備份方式成為條帶備份。使用條帶備份的優點很明確，就是能很好的利用空間，但如果某部分備份丟失或者損壞，那整個備份將無效。
其語句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’,DISK=’E:\mydb.bak’ WITH INIT,CHECKSUM, CONTINUE_ON_ERROR
上述D盤和E盤上的備份是不可分割的。

10、鏡像備份

與條帶備份在多個磁盤上保留同一份備份不同的是，鏡像備份是在不同磁盤上保留多份備份。其語句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’
MIRROR TO DISK=’E:\mydb.bak’
WITH INIT,CHECKSUM,CONTINUE_ON_ERROR

在實際情況下，對日志備份采取鏡像備份方式會比較合適。

11、COPY-ONLY 備份
在差異備份里曾提到過，差異備份是建立在上一個全備份的基礎上的。因此如果在一個事先安排好的備份計劃里，如果在全備份和差異備份之間再進行了一次全備份后，其差異備份會被打斷，如果把臨時全備份刪除掉后，就產生了數據丟失。在SQL SERVER 2005以后，SQL SERVER提供了一個選項copy-only.使用copy-only選項進行的全備份便不會打算原先的備份計劃，語句如下：
SQL code
BACKUP DATABASE mydb TO DISK=’D:\mydb.bak’ WITH INIT,CHECKSUM,COPY_ONLY

三、文件和文件組備份

1、備份數據文件
備份數據文件同樣可以通過BACKUP DATABASE語句來實現。如下：?
SQL code
BACKUP DATABASE mydb FILE=’D:\Data\mydb.ndf’ TO DISK=’E:\Backup\mydbdata.bak’
上述語句相當于數據文件級別的全備份，與數據庫級別的備份類似，文件級別上的備份也有差異備份，當然前提是要有相對應的文件全備份。
差異備份的語句如下：
SQL code
BACKUP DATABASE mydb FILE=’D:\Data\mydb.ndf’ WITH DIFFERENTIAL
TO DISK=’E:\Backup\mydbdata_dif.bak’

5、備份文件組

與單獨備份文件類似，也可以對文件組進行類似的備份操作。備份文件組的方式也有兩種，一種是通過GUI界面指定，一種則是通過T-SQL。

T-SQL的語句如下：?
SQL code
BACKUP DATABASE mydb FILEGROUP=’PRIMARY’ TO DISK=’E:\Backup\mydbpri.bak’

6、不完全備份(partial backup)

在文件組備份上，不完全備份其實相當于完全備份，可以通過指定關鍵字READ_WRITE_FILEGROUPS來實現不完全備份。

語句如下：?
SQL code
BACKUP DATABASE mydb READ_WRITE_FILEGROUPS TO DISK=’D:\mydb.bak’

那不完全備份到底是什么意思呢？什么時候需要不完全備份？如果對一個文件組設置了只讀，而這只讀的文件組又需要進行一次備份，這時，可以不用BACKUP DATABASE語句進行備份，只需要挑個時間停止實例，然后執行不完全備份。

四、數據恢復

1、Restore vs. Recovery

Restore和Recovery是兩個不同的概念，但在數據恢復過程中又是緊密聯系的。

Restore相當于從備份集中重建整個或者部分數據庫，Restore是無法改變數據庫狀態的，如脫機和聯機等。

一旦對數據庫進行了Recovery，則將無法再進行Restore操作。

數據庫恢復的語句如下：?
SQL code
RESTORE DATABASE mydb FROM mydbdevice WITH RECOVERY

顯示指定RECOVERY 或者NORECOVERY是個良好的習慣。

2、備份文件里面的信息

在primary文件里，存儲著與數據庫結構有關的一些信息，如文件位置等。因此備份后，這些信息同樣保留在備份文件里面。如果要恢復到不同的磁盤上或服務器后，需要進行額外的更改。

在SQL Server里，提供了 RESTORE HEADERONLY, RESTORE FILELISTONLY, RESTORE LABELONLY, RESTORE VERIFYONLY 等命令來讀取備份文件的信息。也可以通過MSDB里的表來獲取備份集的相關信息。
A．RESTORE HEADERONLY

SQL code
RESTORE HEADERONLY
FROM DISK='D:\family_20100108.bak'

各字段的含義可以查看聯機叢書。
B.RESOTRE FILELISTONLY
SQL code
RESTORE FILELISTONLY
FROM DISK='D:\family_20100108.bak'

C.RESOTRE LABELONLY
SQL code
RESTORE LABELONLY
FROM DISK='D:\family_20100108.bak'

D.RESOTORE VERIFYONLY
SQL code
RESTORE VERIFYONLY
FROM DISK='D:\family_20100108.bak'

3、從全備份中恢復

需要注意的是，在做恢復之前，應該養成對當前日志進行備份的習慣，否則容易造成數據丟失。SQL Server雖然提供REPLACE選項以便強制恢復，但這樣子末尾日志就丟掉了。
通常，如果未對當前日志進行備份，那么會收到如下錯誤
上述錯誤，提到了使用WITH REPLACE 或者WITH STOPAT命令來完成恢復，但應盡量避免使用這兩個命令。

如果要將數據庫恢復到不同的磁盤上，可以通過GUI頁面指定，也可以通過T-SQL語句來實現。

T-SQL實現的方式如下：
SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak'
WITH MOVE 'Family'TO 'D:\Family.mdf',
MOVE 'Family_Log' TO 'D:\Family.ldf'
WITH RECOVERY

4、恢復到指定的時間點

要恢復到指定的時間點有三種選擇，一種是通過明確指定時間，一種通過指定LSN號，另外一種則是通過創建和指定log marks。
A. TIME

通常情況下，恢復都會有要求恢復到指定時間點的要求，可通過GUI界面來實現，也可以通過T-SQL 來實現
SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak'
WITH NORECOVERY

RESTORE LOG Family
FROM DISK='D:\family_20100108.trn'
WITH RECOVERY,STOPAT 'jan 8,2009 3:10pm'

B. LSN

如果知道確切的LSN號，也可以通過LSN號來恢復指定的LSN。獲取LSN相關信息，可以通過RESOTRE HEADERONLY。這種方式只能通過T-SQL來實現。

SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak'
WITH NORECOVRY

RESTORE LOG Family
FROM DISK='D:\family_20100108.trn'
WITH RECOVRY,STOPATMARK LSN:2433:5422

C. Log Marks
?也可以通過創建Log Mark，可以恢復至指定的Log Marks。例如，創建了一個logmarkexample，則在恢復時，恢復到logmarkexample。

SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak'
WITH NORECOVRY

RESTORE LOG Family
FROM DISK='D:\family_20100108.trn'
WITH RECOVRY,STOPATMARK 'logmarkexample'

5、對鏡像備份或條帶備份的恢復

對鏡像備份而言，每一份備份都是一樣的，因此恢復任何一份備份都可以完成恢復。對條帶備份而言，則需要同時指定所有的條帶備份，這種備份可以比單獨一個備份來得快。

條帶備份例子如下：
SQL code
RESTORE DATABASE Family
FROM DISK='D:\family_20100108.bak',
DISK='D:\family_20100108.bak'
WITH NORECOVRY

6、恢復數據頁

在SQL Server2005以后，SQL Server提供了對數據頁恢復的功能。對數據頁恢復可以在聯機或者脫機狀態下進行
但只能對實際用戶數據頁進行恢復，而其他的頁則無法通過備份來恢復。如Global Allocation Map(GAM), Secondary Global Allocation Map(SGAM), Page Free Space(PFS)等。
恢復數據頁相當于進行完全恢復，不同的是需要指定具體的頁面。
SQL code
RESTORE DATABASE Family PAGE '20:1570,20:1571,20:1572'
FROM DISK='D:\family_20100108.bak'
WITH NORECOVRY

數據頁可以通過MSDB..SUSPECT_PAGE或者DBCC CHECKDB來查找。

7、對系統數據庫的恢復

系統數據庫存儲著一個SQL Server實例上相關數據庫的信息，如果丟失，將會帶來更大的損失。
A. MASTER
Master數據庫的恢復與其他數據庫的恢復是不同的。要恢復MASTER數據庫，需要從將SQL Server切換至單用戶模式，如果無法切換，則停止SQL Server服務，然后用sqlserver –m命令行啟動。
當然，也可以用net start “服務器名” 來啟動SQL Server服務。啟動后，再用SQLCMD命令進行還原

B. MSDB
在MSDB里面存儲得比較多的是SQL Agent里的內容，如作業，調度，操作員，警告等信息；同時還存放SQL Server Integration Service（SSIS）等信息。
其恢復過程與普通數據庫恢復過程是一樣的。并且由于是在簡單模式下，因而其恢復過程更加簡單。

C. MODEL

Model數據庫用來存放創建數據庫時需要的信息，如果有使用MODEL數據庫的話，也需要對其進行備份和恢復。
備份與恢復的過程與普通數據庫一致。
D. Tempdb

Tempdb是不需要備份和恢復的，在每次的啟動過程中，SQL Server會自動清除tempdb，并重新啟動tempdb。在tempdb上需要注意的是其空間規劃，因為某些情況下tempdb會變得非常大，耗盡空間，最終導致SQL Server關掉。

若要修改tempdb的存儲路徑，請使用如下語句：

SQL code
use master

go

Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')

go

Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')

Go

E. Resource

Resource是SQL Server2005以后新引進的一個數據庫，將以前存放于master等其他系統數據庫的部分信息存放于Resource數據庫里。

對Resource不能通過T-SQL或者GUI備份，因為看不到它，要對其進行備份，只能通過手工直接拷貝其物理文件。

6、數據文件備份的恢復

在第三部份里面提到了文件的備份，這種單個或多個文件的備份，其恢復方式與數據庫恢復類似。但要養成一個良好的習慣，在恢復前，備份當前的日志文件。

SQL code
BACKUP LOG Family
TO DISK='E:\Familylog.bak'
WITH NORECOVERY

接著對需要還原的文件進行還原，如 ??

SQL code
RESTORE DATABASE Family
FILE='D:\DATA\Family.mdf'
FROM DISK='E:\Familyprimary.bak'
WITH NORECOVERY

RESTORE LOG Family
FROM DISK='E:\Familylog.bak'
WITH NORECOVERY

RESTORE DATABASE Family
WITH RECOVERY

如果在數據文件上還有差異備份，日志恢復前進行差異備份恢復，如：
SQL code
RESTORE DATABASE Family
FILE='D:\DATA\Family.mdf'
FROM DISK='E:\Familyprimary.bak'
WITH NORECOVERY

RESTORE DATABASE Family
FILE='D:\DATA\Family.mdf'
FROM DISK='E:\Familyprimay.dif'
WITH NORECOVERY

RESTORE LOG Family
FROM DISK='E:\Familylog.bak'
WITH NORECOVERY

RESTORE DATABASE Family
WITH RECOVERY

7、文件組的恢復
與數據文件恢復原理是一致的，只不過是將具體文件文件組。
SQL code
BACKUP LOG Family
TO DISK='E:\Familylog.bak'
WITH NORECOVERY

RESTORE DATABASE Family
FILEGROUP='PRIMARY'
FROM DISK='E:\Familyprimary.bak'
WITH NORECOVERY

RESTORE LOG Family
FROM DISK='E:\Familylog.bak'
WITH NORECOVERY

RESTORE DATABASE Family
WITH RECOVERY

定時任務：
◆1、管理->SQL Server代理->作業(按鼠標右鍵)->新建作業->
◆2、新建作業屬性(常規)->名稱[自定義本次作業的名稱]->啟用的方框內是勾號->
分類處可選擇也可用默認的[未分類(本地)]->所有者默認為登錄SQL Server用戶[也可選其它的登錄]->
描述[填寫本次工作詳細描述內容]；
[ 創建作業分類的步驟:
SQL Server代理->作業->右鍵選所有任務->添加、修改、刪除 ]
◆3、新建作業屬性(步驟)->新建->步驟名[自定義第一步驟名稱]->類型[Transact-SQL(TSQL)腳本]->
數據庫[要操作的數據庫]->命令
[如果是簡單的SQL直接寫進去即可，也可用打開按鈕輸入一個已寫好的*.sql文件
如果要執行存儲過程，填
exec p_procedure_name v_parameter1,[ v_parameter2…v_parameterN]
->確定
(如果有多個步驟，可以再次調用下面的新建按鈕；也可以對已有的多個步驟插入、編輯、刪除)；
◆4、建作業屬性(調度)->新建調度->名稱[自定義調度名稱]->啟用的方框內是勾號->調度->反復出現->
更改[調度時間表]->確定
(如果只要保存此作業，不要定時做可以把啟用的方框內是勾號去掉)；
◆5、建作業屬性(通知)->用默認的通知方法就好[當作業失敗時，寫入Windows應用程序系統日志] ->確定。
跟作業執行相關的一些SQL Server知識：
SQLSERVERAGENT服務必須正常運行，啟動它的NT登錄用戶要跟啟動SQL Server數據庫的NT登錄用戶一致。
點作業右鍵可以查看作業執行的歷史記錄情況，也可以立即啟動作業和停止作業。