程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQLServer恢復表級數據詳解，sqlserver詳解

SQLServer恢復表級數據詳解，sqlserver詳解

編輯：更多數據庫知識

SQLServer恢復表級數據詳解，sqlserver詳解

最近幾天，公司的技術維護人員頻繁讓我恢復數據庫，因為他們總是少了where條件，導致update、delete出現了無法恢復的後果，加上那些庫都是幾十G。恢復起來少說也要十幾分鐘。為此，找了一些資料和工作總結，給出一下幾個方法，用於快速恢復表，而不是庫，但是切記，防范總比亡羊補牢好。

在生產環境或者開發環境，往往都有某些非常重要的表。這些表存放了核心數據。當這些表出現數據損壞時，需要盡快還原。但是，正式環境的數據庫往往都是非常大的，統計數據表明，1T的數據庫還原時間接近24小時，所以因為一個表而還原一個庫，不單空間，甚至時間上都是一個很大的挑戰。本文介紹如何恢復單表，而不需要恢復整個庫。

現在假設一個表：TEST_TABLE。我們需要盡快恢復這個表，並且把恢復過程中對其他表和用戶的影響降到最低。

SQLServer（特別是2008以後），具有很多備份及恢復功能：完整、部分、文件、差異和事務備份。而恢復模式的選擇嚴重影響備份策略和備份類型。

下面是幾個可供參考的方案，但是記住，各有好壞，應該按照實際需要選擇：

方案1：恢復到一個不同的數據庫：

對於小數據庫來說不失為一種好的辦法，用備份還原一個新的庫，並把新庫中的表數據同步回去。你可以做完整恢復，或者時間點恢復。但是對於大數據庫，是非常耗時和耗費磁盤空間的。這個方法僅僅用於還原數據，在還原數據（就是同步數據）的時候，你要考慮觸發器、外鍵等因素。

方案2：使用STOPAT來還原日志：

你可能想恢復最近的數據庫備份，並回滾到某個時間點，即發生意外前的某個時刻。此時可以使用STOPAT子句，但是前提是必須為完整或大容量日志恢復模式。下面是例子：

RESTORE DATABASE 需要恢復的數據庫 
 FROM 數據庫備份 
 WITH FILE=3, NORECOVERY ; 
 
RESTORE LOG需要恢復的數據庫 
 FROM數據庫備份 
 WITH FILE=4, NORECOVERY, STOPAT = 'Oct 22, 2012 02:00 AM' ; 
 
RESTORE DATABASE 需要恢復的數據庫 WITH RECOVERY ;

注意：這種方法的主要缺點是會覆蓋掉從stopat指定時間點之後所修改的所有數據。所以要衡量好得失。

方案3：數據庫快照：

創建數據庫快照。當發生意外時，可以從快照中直接獲取原來的數據。但是必須是在發生意外之前創建的快照。這在核心表不經常更新，特別是有規律更新時很有用。但是當表經常、不定期被更新，或者很多用戶在訪問時，這種方法就不可取了。當需要使用這種方法時，記得在每次更新前先創建快照。

方案4：使用視圖：

你可以創建一個新的數據庫，並把TEST_TABLE移動到這個庫裡面。當你需要恢復的時候，你只需要恢復這個非常小的數據庫即可。訪問源數據庫的數據時，最簡單的方法就是創建一個視圖，選擇TEST_TABLE表中所有列的所有數據。但是注意這個方法需要在創建視圖前，重命名或者刪除源數據庫的表：

USE 需要恢復的數據庫 ; 
GO 
CREATE VIEW TEST_TABLE 
AS 
  SELECT * 
  FROM  備份數據庫.架構名.TEST_TABLE ; 
GO

使用這種方法，可以對視圖使用SELECT /INSERT/UPDATE/DELETE語句，就像直接操作實體表似得。當TEST_TABLE更改時，要使用SP_REFRESHVIEW存儲過程來更新元數據。

方案5：創建同義詞（Synonym）：

和方案4類似，把表移到另外一個數據庫，然後對源數據庫的這個表創建一個同義詞：

USE 需要恢復的數據庫 ; 
GO 
CREATE SYNONYM TEST_TABLE 
FOR 新數據庫.架構名.TEST_TABLE ; 
GO

方案6：使用BCP保存數據：

你可以創建一個作業，使用BCP定期導出數據。但是這種方法的缺點和方案1類似，需要找到哪天的文件並導進去，同時要考慮觸發器和外鍵問題。

各種方法的對比：這個方法的有點就是你不需要擔心元數據更新所帶來的結構變更不及時。但是這個方法的問題就是不能在DDL語句中引用同義詞，或者不能在鏈接服務器中找到。

方法優點缺點還原數據庫快且容易適用於小庫，且要注意觸發器和外鍵等還原日志能指定時間點所有時間點後的新數據會被覆蓋數據庫快照當表不是經常更新時很有用當表並行更新時，快照容易出現問題視圖把表的數據於庫分開，沒有數據丟失元數據需要周期性更新，並要定期維護新數據庫同義詞把表的數據於庫分開，沒有數據丟失在鏈接服務器上不能用，並要定期維護新數據庫 BCP 擁有表的專用備份需要額外的空間、還會出現觸發器、外鍵等問題

總結：

良好的編程習慣和良好的備份機制才是解決問題的根本，以上的措施都僅僅是一個亡羊補牢的辦法。可能有人說SQLServer 新版本不是有部分還原嗎？我們來看看聯機叢書的說明：

可以看到，其他這種方法很難還原一個表，但是當庫小的時候，倒可以試試。

SQL Server數據庫為何不可以恢復單表數據

比如，你備份了AdventureWorks數據庫，現的你只恢復裡面Vendor表數據。不幸的是，SQL Server本身並不支持這樣還原，你需要從第三方提供的工具中來執行這樣的任務。提供這種功能的程序都是一些SQL Server第三方備份工具。它們可以讓你從備份文件中抽取或是讀取單個表數據。但是這裡有一點要注意是，假如你選擇恢復一個完整數據庫，那麼你選擇要恢復的數據有可能與你獲取的數據不同。原因就是因為當你執行完整數據庫備份時，備份文件包括數據文件和事務日志文件。有一些已經提交的事務還沒有寫入數據文件，在這種情況下，事務僅僅存儲在事務日志中，或者部份數據存儲在數據文件。當你使用第三方程序恢復所選擇的單表數據時，僅僅只能從備份文件中的數據庫文件中獲取數據，事務日志中的新數據將會被忽略。下圖顯示了在備份期間沒有checkpoint的備份序列。在備份開始之前，如果事務還沒有被寫到數據文件中，那會怎麼樣呢？答案是不確定，因為當備份開始後，SQL Server會自動執行checkpoint操作。當checkpoint期間，髒數據頁已經被寫到磁盤上了。當我們從備份文件恢復單獨一個表的數據時，我們能不能認為要恢復的數據就處於備份開始處呢？這取決於備份過程。如果在備份期間沒有事務在運行，那就沒問題。如果這期間有事務在運行而且checkpoint也沒有被觸發，那也沒問題。但是如果在備份期間有事務在執行，那我們恢復的數據也許就會包括備份起始時的數據，而且在備份期間數據被提交了的話，如果在備份期間發生了checkpoint而且數據也被寫到了數據頁，而這個數據頁在checkpoint發生時沒有被備份。（最後這句實在不知道怎麼翻譯才好，附上原文如下：If however there were transactions performed during the backup, the data we recover may include data at the beginning of the backup, and data that were committed during the backup IF checkpoints occurred during the backup AND the data was written to pages that were not yet backed up yet at the time the checkpoint occurred.）下圖顯示了帶checkpoint的備份序列當你進行表級別的恢復之前，要先考慮一些事情。一個完整備份包括所有的數據，還有數據庫所有直到備份結尾的改變。一個單獨的數據文件無法映射出指定時刻數據庫的狀態。因此，表級別的恢復包括舊、髒數據。