SQLServer恢復(fù)表級數(shù)據(jù)詳解

更新時(shí)間：2014年08月10日 12:00:23 投稿：mdxy-dxy

這篇文章主要介紹了SQLServer中用于快速恢復(fù)表，而不是庫，但是切記，防范總比亡羊補(bǔ)牢好,需要的朋友可以參考下

最近幾天，公司的技術(shù)維護(hù)人員頻繁讓我恢復(fù)數(shù)據(jù)庫，因?yàn)樗麄兛偸巧倭藈here條件，導(dǎo)致update、delete出現(xiàn)了無法恢復(fù)的后果，加上那些庫都是幾十G。恢復(fù)起來少說也要十幾分鐘。為此，找了一些資料和工作總結(jié)，給出一下幾個(gè)方法，用于快速恢復(fù)表，而不是庫，但是切記，防范總比亡羊補(bǔ)牢好。

在生產(chǎn)環(huán)境或者開發(fā)環(huán)境，往往都有某些非常重要的表。這些表存放了核心數(shù)據(jù)。當(dāng)這些表出現(xiàn)數(shù)據(jù)損壞時(shí)，需要盡快還原。但是，正式環(huán)境的數(shù)據(jù)庫往往都是非常大的，統(tǒng)計(jì)數(shù)據(jù)表明，1T的數(shù)據(jù)庫還原時(shí)間接近24小時(shí)，所以因?yàn)橐粋€(gè)表而還原一個(gè)庫，不單空間，甚至?xí)r間上都是一個(gè)很大的挑戰(zhàn)。本文介紹如何恢復(fù)單表，而不需要恢復(fù)整個(gè)庫。

現(xiàn)在假設(shè)一個(gè)表：TEST_TABLE。我們需要盡快恢復(fù)這個(gè)表，并且把恢復(fù)過程中對其他表和用戶的影響降到最低。

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

下面是幾個(gè)可供參考的方案，但是記住，各有好壞，應(yīng)該按照實(shí)際需要選擇：

方案1：恢復(fù)到一個(gè)不同的數(shù)據(jù)庫：

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

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

你可能想恢復(fù)最近的數(shù)據(jù)庫備份，并回滾到某個(gè)時(shí)間點(diǎn)，即發(fā)生意外前的某個(gè)時(shí)刻。此時(shí)可以使用STOPAT子句，但是前提是必須為完整或大容量日志恢復(fù)模式。下面是例子：

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

注意：這種方法的主要缺點(diǎn)是會(huì)覆蓋掉從stopat指定時(shí)間點(diǎn)之后所修改的所有數(shù)據(jù)。所以要衡量好得失。

方案3：數(shù)據(jù)庫快照：

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

方案4：使用視圖：

你可以創(chuàng)建一個(gè)新的數(shù)據(jù)庫，并把TEST_TABLE移動(dòng)到這個(gè)庫里面。當(dāng)你需要恢復(fù)的時(shí)候，你只需要恢復(fù)這個(gè)非常小的數(shù)據(jù)庫即可。訪問源數(shù)據(jù)庫的數(shù)據(jù)時(shí)，最簡單的方法就是創(chuàng)建一個(gè)視圖，選擇TEST_TABLE表中所有列的所有數(shù)據(jù)。但是注意這個(gè)方法需要在創(chuàng)建視圖前，重命名或者刪除源數(shù)據(jù)庫的表：

USE 需要恢復(fù)的數(shù)據(jù)庫 ; 
GO 
CREATE VIEW TEST_TABLE 
AS 
  SELECT * 
  FROM  備份數(shù)據(jù)庫.架構(gòu)名.TEST_TABLE ; 
GO

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

方案5：創(chuàng)建同義詞（Synonym）：

和方案4類似，把表移到另外一個(gè)數(shù)據(jù)庫，然后對源數(shù)據(jù)庫的這個(gè)表創(chuàng)建一個(gè)同義詞：

USE 需要恢復(fù)的數(shù)據(jù)庫 ; 
GO 
CREATE SYNONYM TEST_TABLE 
FOR 新數(shù)據(jù)庫.架構(gòu)名.TEST_TABLE ; 
GO

方案6：使用BCP保存數(shù)據(jù)：

你可以創(chuàng)建一個(gè)作業(yè)，使用BCP定期導(dǎo)出數(shù)據(jù)。但是這種方法的缺點(diǎn)和方案1類似，需要找到哪天的文件并導(dǎo)進(jìn)去，同時(shí)要考慮觸發(fā)器和外鍵問題。

各種方法的對比：這個(gè)方法的有點(diǎn)就是你不需要擔(dān)心元數(shù)據(jù)更新所帶來的結(jié)構(gòu)變更不及時(shí)。但是這個(gè)方法的問題就是不能在DDL語句中引用同義詞，或者不能在鏈接服務(wù)器中找到。

方法	優(yōu)點(diǎn)	缺點(diǎn)
還原數(shù)據(jù)庫	快且容易	適用于小庫，且要注意觸發(fā)器和外鍵等
還原日志	能指定時(shí)間點(diǎn)	所有時(shí)間點(diǎn)后的新數(shù)據(jù)會(huì)被覆蓋
數(shù)據(jù)庫快照	當(dāng)表不是經(jīng)常更新時(shí)很有用	當(dāng)表并行更新時(shí)，快照容易出現(xiàn)問題
視圖	把表的數(shù)據(jù)于庫分開，沒有數(shù)據(jù)丟失	元數(shù)據(jù)需要周期性更新，并要定期維護(hù)新數(shù)據(jù)庫
同義詞	把表的數(shù)據(jù)于庫分開，沒有數(shù)據(jù)丟失	在鏈接服務(wù)器上不能用，并要定期維護(hù)新數(shù)據(jù)庫
BCP	擁有表的專用備份	需要額外的空間、還會(huì)出現(xiàn)觸發(fā)器、外鍵等問題