SQLServer數(shù)據(jù)庫(kù)誤操作恢復(fù)的方法
前言
在日常開(kāi)發(fā)工作中,有可能會(huì)存在數(shù)據(jù)庫(kù)的誤操作,比如不小心刪除了一個(gè)表或者刪除了一堆數(shù)據(jù),這個(gè)時(shí)候我們就需要將數(shù)據(jù)庫(kù)進(jìn)行恢復(fù),恢復(fù)到誤操作以前的狀態(tài),而這篇文章就是主要如何在誤操作之后,恢復(fù)數(shù)據(jù)庫(kù)的數(shù)據(jù)。
一、恢復(fù)數(shù)據(jù)實(shí)例
1.創(chuàng)建初始數(shù)據(jù)
use master if exists (select * from sys.databases where name='test') drop database test; create database test; use test create table UserInfo ( id int, name varchar(50), nickname varchar(50), ); go insert into UserInfo values (1,'張三','小張'); insert into UserInfo values (2,'張三2','小張'); insert into UserInfo values (3,'李四','小李'); insert into UserInfo values (4,'王五','小王'); insert into UserInfo values (5,'趙六','小趙'); select * from UserInfo;
2.保證數(shù)據(jù)恢復(fù)的前提條件
前提1 - 數(shù)據(jù)庫(kù)創(chuàng)建時(shí)便已設(shè)置恢復(fù)模式為完整
對(duì)于任何環(huán)境下,新建數(shù)據(jù)庫(kù)后,我們都需要先檢查數(shù)據(jù)庫(kù)的恢復(fù)模式,確認(rèn)數(shù)據(jù)庫(kù)的恢復(fù)模式是【完整恢復(fù)模式】,這是數(shù)據(jù)庫(kù)數(shù)據(jù)得以恢復(fù)的基礎(chǔ)。如果等到出問(wèn)題,再想到這個(gè)就為時(shí)已晚了。
修改恢復(fù)模式如下圖所示:
選中數(shù)據(jù)庫(kù),然后右鍵菜單=》選擇【屬性】=》屬性頁(yè)選擇【選項(xiàng)】=》恢復(fù)模式哪一項(xiàng),選擇【完整】=》點(diǎn)擊【確定】
數(shù)據(jù)庫(kù)一般默認(rèn)恢復(fù)模式就是【完整】恢復(fù)模式。
腳本檢查恢復(fù)模式:SELECT recovery_model,recovery_model_desc FROM sys.databases WHERE name ='test';
如果查詢(xún)結(jié)果如下,則表示數(shù)據(jù)恢復(fù)模式配置沒(méi)有問(wèn)題。
前提2 - 至少做過(guò)一次完整的備份
數(shù)據(jù)庫(kù)數(shù)據(jù)恢復(fù)是需要基于數(shù)據(jù)的完整備份上進(jìn)行的,如果沒(méi)有一次完整的數(shù)據(jù)備份,那么數(shù)據(jù)備份無(wú)從談起,所以,創(chuàng)建完一次數(shù)據(jù)庫(kù)后,需強(qiáng)制性做一次完整的備份。
數(shù)據(jù)完整備份操作如下圖所示:
選中數(shù)據(jù)庫(kù)=》右鍵菜單,選擇【任務(wù)】=》選擇【備份】=》備份類(lèi)型,選擇【完整】=》確定
擴(kuò)展內(nèi)容:可以通過(guò)一下sql查詢(xún)所有的數(shù)據(jù)庫(kù)備份信息
SELECT database_name,recovery_model,name, backup_finish_date,type FROM msdb.dbo.backupset
如:本人剛才操作了兩次數(shù)據(jù)完整備份和一次事務(wù)日志備份,查詢(xún)結(jié)果如下,
type為D表示數(shù)據(jù)備份,L表示事務(wù)日志備份:
3.模擬不小心誤操作
再次強(qiáng)調(diào):數(shù)據(jù)恢復(fù)的前提條件中修改備份模式以及強(qiáng)制做一次完整的數(shù)據(jù)和事務(wù)日志備份,是我們?cè)诔霈F(xiàn)問(wèn)題之前必須做過(guò)的操作,否則就沒(méi)有什么恢復(fù)可言了。
現(xiàn)在我們模擬做一下誤操作:
--模擬誤操作 2022-11-23 14.25 開(kāi)始的誤操作,記一下誤操作事件,以便后續(xù)演示數(shù)據(jù)恢復(fù) --本意刪除張三2的,但是條件不當(dāng),將不該刪除的數(shù)據(jù)也刪除了 delete from UserInfo where nickname='小張'; insert into UserInfo values (110,'錯(cuò)誤數(shù)據(jù)','小錯(cuò)誤'); --不小心新建了一個(gè)表格 select * into UserInfo2 from UserInfo;
現(xiàn)在我們比對(duì)一下誤操作前后數(shù)據(jù)
4.開(kāi)始恢復(fù)
出現(xiàn)誤操作之后,具體恢復(fù)步驟如下:
1、首先將數(shù)據(jù)庫(kù)限制為單個(gè)用戶(hù)訪(fǎng)問(wèn)
出現(xiàn)誤操作后,我們需要切斷其他用戶(hù)對(duì)數(shù)據(jù)庫(kù)在進(jìn)行操作產(chǎn)生數(shù)據(jù),造成更大的問(wèn)題,因此需要先將數(shù)據(jù)庫(kù)限制為單個(gè)用戶(hù)訪(fǎng)問(wèn),與外界隔絕開(kāi)。
具體操作如下:
當(dāng)我們將限制訪(fǎng)問(wèn)設(shè)置為【SINGLE_USER】后,我們會(huì)發(fā)現(xiàn) 數(shù)據(jù)庫(kù)后面會(huì)附加上【單個(gè)用戶(hù)】標(biāo)志。
2、做一次事務(wù)日志備份(備份日志尾部)
- 首先選擇數(shù)據(jù)庫(kù),右鍵菜單,選擇【任務(wù)】=》選擇【備份】
- 進(jìn)入備份界面,
- 在【常規(guī)】這一項(xiàng)設(shè)置中 ,將備份類(lèi)型選擇【事務(wù)日志】
- 在【介質(zhì)選項(xiàng)】這一項(xiàng)設(shè)置中,按照下圖操作步驟進(jìn)行勾選和操作
- 最后點(diǎn)擊【確定】,此時(shí)我們會(huì)發(fā)現(xiàn)數(shù)據(jù)庫(kù)出現(xiàn)了【正在還原…】的標(biāo)志
- 具體操作步驟如下圖所示:
如果無(wú)法備份,則查詢(xún)一下誰(shuí)在占用:
SELECT * FROM sys.sysprocesses WHERE dbid=DB_ID('test')
然后使用kill 語(yǔ)句將占用者清除即可,如 kill 56,56代表占用者的spid
完成以上操作后,繼續(xù)進(jìn)行備份。
3、還原完整數(shù)據(jù)備份
完成上述事務(wù)日志的備份后,我們就需要將數(shù)據(jù)進(jìn)行還原。
- 首先選擇數(shù)據(jù)庫(kù),右鍵菜單=》選擇【任務(wù)】=》選擇【還原】=》選擇【文件和文件組】
- 進(jìn)入【還原文件和文件組】界面
- 【常規(guī)】這一項(xiàng)中 在備份集列表中 選擇 文件類(lèi)型為【行數(shù)據(jù)的】并且類(lèi)型為【完整】,備份的完成時(shí)間距離誤操作最近的一次備份。
- 【選項(xiàng)】這一項(xiàng)中 選擇【不對(duì)數(shù)據(jù)庫(kù)執(zhí)行任何操作,不回滾未提交的事務(wù)】這一項(xiàng)
- 最后點(diǎn)擊【確定】即可
- 具體操作如下圖所示:
4、還原事務(wù)日志備份
還原完完整數(shù)據(jù)備份后,我們需要還原事務(wù)日志,因?yàn)閿?shù)據(jù)庫(kù)最終需要根據(jù)事務(wù)日志與完整備份的數(shù)據(jù)進(jìn)行比對(duì)后進(jìn)行數(shù)據(jù)的恢復(fù)操作。
- 首先選擇數(shù)據(jù)庫(kù),右鍵菜單=》選擇【任務(wù)】=》選擇【還原】=》選擇【事務(wù)日志】
- 進(jìn)入還原事務(wù)日志界面
- 【常規(guī)】這一項(xiàng)設(shè)置中,選擇需要還原的事務(wù)日志備份(選擇誤操作后備份的那個(gè)事務(wù)日志)
- 然后點(diǎn)擊時(shí)間點(diǎn)后面的按鈕,打開(kāi)時(shí)間還原窗口,設(shè)置需要還原的時(shí)間點(diǎn)
- 設(shè)置完還原的時(shí)間點(diǎn)后,在【選項(xiàng)】這一項(xiàng)設(shè)置中,設(shè)置恢復(fù)狀態(tài)為回滾未提供事務(wù),使用數(shù)據(jù)庫(kù)處于可以使用的狀態(tài)
- 最后點(diǎn)擊【確定】即可
- 具體操作如下圖所示:
至此,數(shù)據(jù)已經(jīng)恢復(fù)成功。
5、最后恢復(fù)數(shù)據(jù)庫(kù)的限制訪(fǎng)問(wèn)設(shè)置
將限制訪(fǎng)問(wèn),從【SINGLE_USER】修改為【MULTI_USER】即可
到此這個(gè)數(shù)據(jù)恢復(fù)的完整流程結(jié)束。
二、恢復(fù)數(shù)據(jù)原理
1.數(shù)據(jù)庫(kù)文件類(lèi)型
數(shù)據(jù)庫(kù)中的文件類(lèi)型:
類(lèi)型 | 作用 |
---|---|
mdf (primary data file) | 主要數(shù)據(jù)文件,數(shù)據(jù)庫(kù)系統(tǒng)的可實(shí)時(shí)操作/讀取的數(shù)據(jù)文件,也可作為物理備份文件使用 |
ndf (secondary data files) | 次要數(shù)據(jù)文件 |
ldf (Log data files) | 事務(wù)日志文件,用于記錄數(shù)據(jù)庫(kù)的事務(wù)日志信息 |
bak | 數(shù)據(jù)庫(kù)備份文件 |
- 以上有mdf,ndf,ldf 以及bak 四種文件類(lèi)型,其中一般我們創(chuàng)建的數(shù)據(jù)庫(kù)都會(huì)包含mdf 和ldf 兩個(gè)文件,不需要手動(dòng)創(chuàng)建,這兩個(gè)是一套數(shù)據(jù),可以通過(guò)分離的方式拷貝出來(lái)作為備份,還原的時(shí)候通過(guò)數(shù)據(jù)庫(kù)的“附加”功能即可還原。
- bak自成一套,bak文件是通過(guò)使用數(shù)據(jù)庫(kù)的“備份”功能而備份出來(lái)的文件,里面包含了數(shù)據(jù)和日志文件,并且備份時(shí)做了壓縮,所以同一個(gè)數(shù)據(jù)庫(kù)的bak文件比在運(yùn)行的mdf數(shù)據(jù)文件+ldf日志文件要小。
2.使用bak恢復(fù)數(shù)據(jù)原理
首先我們要了解 事務(wù)日志中,記錄了數(shù)據(jù)庫(kù)自創(chuàng)建之初數(shù)據(jù)庫(kù)所有的操作日志。
例如:
- 2022-10-10 創(chuàng)建了數(shù)據(jù)庫(kù),此時(shí)我們第一次做了完整備份bak(包含數(shù)據(jù)+日志)
- 2022-10-11 我們又做了一次完整備份bak(包含數(shù)據(jù)+日志)
- 2022-10-12
- 12:00 做了誤操作,此時(shí)還不知曉,
- 12:05 我們發(fā)現(xiàn)自己誤操作了,馬上設(shè)置訪(fǎng)問(wèn)權(quán)限,進(jìn)行事務(wù)日志備份(這很關(guān)鍵),此時(shí)我們就有了2022-10-11所有的完整數(shù)據(jù),以及到2022-10-12 12:05所有的事務(wù)日志。那么我們就可以根據(jù)2022-10-11的完整數(shù)據(jù)以及2022-10-12 12:05 的事務(wù)日志去反推12:00的數(shù)據(jù)。因此我們需要做的第一個(gè)操作先還原2022-10-11的完整數(shù)據(jù)備份,然后還原2022-10-12 12:05事務(wù)日志備份,并且設(shè)置還原的時(shí)間點(diǎn)。最后數(shù)據(jù)庫(kù)系統(tǒng)會(huì)根據(jù)完整數(shù)據(jù)以及事務(wù)日志備份和設(shè)置的還原時(shí)間點(diǎn)去恢復(fù)數(shù)據(jù)。
總結(jié)
到此這篇關(guān)于SQLServer數(shù)據(jù)庫(kù)誤操作恢復(fù)的方法的文章就介紹到這了,更多相關(guān)SQLServer數(shù)據(jù)庫(kù)恢復(fù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
參考
SQLServer 2008以上誤操作數(shù)據(jù)庫(kù)恢復(fù)方法——日志尾部備份
數(shù)據(jù)庫(kù)誤操作恢復(fù)實(shí)現(xiàn)原理
- sqlserver數(shù)據(jù)庫(kù)加密后無(wú)法使用MDF,LDF,log文件名稱(chēng)被修改的數(shù)據(jù)恢復(fù)
- SQLServer數(shù)據(jù)庫(kù)處于恢復(fù)掛起狀態(tài)的解決辦法
- 刪除sqlserver數(shù)據(jù)庫(kù)日志和沒(méi)有日志的數(shù)據(jù)庫(kù)恢復(fù)辦法
- SQLServer2005 沒(méi)有日志文件(*.ldf) 只有數(shù)據(jù)文件(*.mdf) 恢復(fù)數(shù)據(jù)庫(kù)的方法
- 設(shè)置密碼保護(hù)的SqlServer數(shù)據(jù)庫(kù)備份文件與恢復(fù)文件的方法
- 數(shù)據(jù)庫(kù)備份 SQLServer的備份和災(zāi)難恢復(fù)
- asp 在線(xiàn)備份與恢復(fù)sqlserver數(shù)據(jù)庫(kù)的代碼
- sqlserver 數(shù)據(jù)庫(kù)日志備份和恢復(fù)步驟
- 如何強(qiáng)制刪除或恢復(fù)SQLServer正在使用的數(shù)據(jù)庫(kù)
相關(guān)文章
Sql Server使用cursor處理重復(fù)數(shù)據(jù)過(guò)程詳解
本節(jié)主要介紹了Sql Server cursor的使用,以處理重復(fù)數(shù)據(jù)為例,需要的朋友可以參考下2014-08-08SQL server服務(wù)顯示遠(yuǎn)程過(guò)程調(diào)用失敗的解決方法
這篇文章主要為大家介紹了SQL server服務(wù)顯示遠(yuǎn)程過(guò)程調(diào)用失敗的解決方法,還為大家提供了解決SQL SERVER 2008 R2配置管理器出現(xiàn)“遠(yuǎn)程過(guò)程調(diào)用失敗”(0x800706be)錯(cuò)誤提示的方案,感興趣的小伙伴們可以參考一下2016-05-05MSSQL內(nèi)外連接(INNER JOIN)語(yǔ)句詳解
這幾天重新溫習(xí)了一下SQL的書(shū)本,現(xiàn)在的思路應(yīng)該是很清楚了,現(xiàn)在把自己的理解發(fā)出來(lái)給大家溫習(xí)下。希望和我一樣對(duì)SQL的連接語(yǔ)句不太理解的朋友能夠有所幫助2006-11-11用sql語(yǔ)句實(shí)現(xiàn)分離和附加數(shù)據(jù)庫(kù)的方法
對(duì)于分離一個(gè)數(shù)據(jù)庫(kù)來(lái)說(shuō),我們可以用Manage Studio界面或者存儲(chǔ)過(guò)程。但是對(duì)于每一種方法都必須保證沒(méi)有用戶(hù)使用這個(gè)數(shù)據(jù)庫(kù).接下來(lái)所講的都是對(duì)于用命令來(lái)分離或附加一個(gè)數(shù)據(jù)庫(kù)。2010-03-03Windows2012配置SQLServer2014AlwaysOn的圖解
SQLserver 2014 AlwaysOn增強(qiáng)了原有的數(shù)據(jù)庫(kù)鏡像功能,使得先前的單一數(shù)據(jù)庫(kù)故障轉(zhuǎn)移變成以組(多個(gè)數(shù)據(jù))為單位的故障轉(zhuǎn)移。接下來(lái)通過(guò)本文給大家介紹Windows2012配置SQLServer2014AlwaysOn的方法,感興趣的朋友一起學(xué)習(xí)吧2016-04-04sql server代理中作業(yè)執(zhí)行SSIS包失敗的解決辦法
這篇文章主要介紹了sql server代理中作業(yè)執(zhí)行SSIS包失敗的解決辦法,sql2005如何用dtexec運(yùn)行ssis(DTS)包?本文講的非常詳細(xì),小伙伴們一起學(xué)習(xí)吧2015-09-09SQL Server的Descending Indexes降序索引實(shí)例展示
在涉及多字段排序的復(fù)雜查詢(xún)中,合理使用降序索引可以顯著提升SQLServer的查詢(xún)效率,本文通過(guò)構(gòu)建實(shí)際的查詢(xún)案例,展示了如何在SQLServer中建立并利用降序索引優(yōu)化查詢(xún)性能,感興趣的朋友一起看看吧2024-09-09