SQL Server 資料恢複到指點時間點(完整恢複)

標籤：sql server 資料恢複到指點時間點(完整恢複)

SQL Server 資料恢複到指點時間點(完整恢複)

說到資料庫恢複，其實我們一般最常見的有兩種，一種就是簡單恢複，通過備份的bak檔案直接恢複資料庫，缺點就是有可能資料丟失。另外一種就是通過備份的資料+交易記錄還原資料。但是後者還原的話我們需要保證交易記錄的完整性。我們今天就主要介紹的是第二種，所有的操作過程我們使用的是SSMS進行操作的。

註：使用第二種方法還原資料的時候需要注意的問題：1、要準備好之前的完整備份的資料，2、備份全新的交易記錄。3、通過完整備份的資料進行完整還原。4、通過備份的全新的交易記錄進行指定時間點的資料還原。

我們首先建立一個測試資料庫---DB2，然後建立一個表info

定義欄位資訊

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYPfx3UlAAAFOF1wXcTw830.png" height="350" />

我們插入資料

select * from infoinsert into info(idcode,age)values(‘zs‘,18)insert into info(idcode,age)values(‘ls‘,20)insert into info(idcode,age)values(‘gwl‘,27)

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M02/8E/ED/wKioL1jPYPixcRfMAAHceio_Daw457.png" height="422" />

接下來我們備份資料，備份路勁D:\DB_BACKUP

右擊資料庫---任務--備份

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYPujPXpNAAICyJc33OY564.png" height="484" />

備份類型：完整。

備份路勁：D:\db_backup\db2.bak

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYP2BRPCRAAHkQEx3skg387.png" height="419" />

備份完成

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M02/8E/EF/wKiom1jPYQDzFn9nAAH3xCeeBUA120.png" height="413" />

備份完成的檔案

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYQPSDeyuAACDVEIybPA261.png" height="312" />

成功備份後，我們增加幾條資料，然後刪除幾條資料(我們需要注意時間點，方便後面的操作)

此時就當是我們誤操作結果；誤操作後，我們正常還原了就可以了

需要注意的是，輸入的寫入和刪除有可能是不同的使用者進行操作的，所以 通過還原肯定是資料丟失了，所以我們通過完全備份恢複完成後，還需要通過log進行還原。

目前時間時21：32

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYQTihJ1oAADqKX8cwa0080.png" height="320" />

我們插入了幾條資料

select * from infoinsert into info(idcode,age)values(‘xll‘,118)insert into info(idcode,age)values(‘wc‘,210)insert into info(idcode,age)values(‘lc‘,127)

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYQXDIWP8AAIDNc2yB4Y711.png" height="456" />

我們在過幾分鐘進行資料刪除--刪除2兩條測試資料

目前時間為：

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M02/8E/ED/wKioL1jPYQbiLUp8AAEwF6MKj-k906.png" height="298" />

接下來我們刪除兩條資料

select * from infodelete info where idcode = ‘ls‘delete info where idcode = ‘gwl‘

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M02/8E/ED/wKioL1jPYQejZ0UJAAIXe1yIwZc055.png" height="478" />

此時我們探索資料有異常所以需要通過備份進行還原。但是我們的備份之後，做了一些資料的插入，如果用之前備份的恢複的話，資料肯定有丟失，所以我們還需要藉助日誌進行還原。

所以我們需要通過完整備份進行恢複，然後通過log進行恢複，然後恢複到指定的時間點。

在恢複完整備份之前，我們需要進行一次全新的交易記錄備份。

其實備份交易記錄的目錄是為了將其寫入截斷，保證資料的完整性

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYQizQBIAAAEo0Miro8I366.png" height="287" />

資料庫---右擊--任務---備份---備份類型--交易記錄

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYQnxVISwAAD7Daiad9o233.png" height="484" />

備份成功後

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYQmhXDduAACFE2TEghM121.png" height="259" />

此時我們需要通過完整備份進行還原以下資料

資料庫---任務---還原--資料庫

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M02/8E/EF/wKiom1jPYQuiAdSJAAG_xXnj8bc809.png" height="484" />

選擇裝置---瀏覽備份的資料庫檔案

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M02/8E/EF/wKiom1jPYQ6wXedJAAHn07CVx4c879.png" height="470" />

記住，我們一定要在選項中選擇 norecovery  
恢複狀態：RESTORE WITH NORECOVERY

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYRHxUOVDAAIsdDDgJ3g661.png" height="464" />

開始還原提示以下資訊：

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYRTQdoUwAAJbOVsLqtc732.png" height="464" />

我們也可以使用以下指令碼進行還原

USE [master]BACKUP LOG [DB2] TO DISK = N‘D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\DB2_LogBackup_2017-03-19_22-40-40.bak‘ WITH NOFORMAT, NOINIT, NAME = N‘DB2_LogBackup_2017-03-19_22-40-40‘, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5RESTORE DATABASE [DB2] FROM DISK = N‘D:\DB_BACKUP\DB2.bak‘ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5GO

出現以上問題，我們有兩種解決方案

1、還原資料庫時，點擊選擇頁上的選項，勾選覆蓋現有資料庫(WITH REPLACE)，點確定後即可成功還原資料庫（推薦此方法）。

650) this.width=650;" title="0-365228085" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="0-365228085" src="http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYRXxzxhtAAB83rWHCrQ064.jpg" height="201" />

2、進行還原作業時，點擊選擇頁上的選項，勾選保持來源資料庫處於正在還原狀態(BACKUP LOG WITH NORECOVERY)，即可解決問題。

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M01/8E/ED/wKioL1jPYRjhot_HAAI3Af8MVLE043.png" height="465" />

我們選擇第二種方法可以正常恢複成功

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M01/8E/ED/wKioL1jPYRvTo-lZAAG8AsLNEz4071.png" height="463" />

此時我們查看到資料庫一直再提示---正在還原中，該現象為正常的。

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/EF/wKiom1jPYRyx22BrAAERV3efb-0263.png" height="459" />

接下來我們要通過交易記錄進行資料還原。

資料庫---任務---還原---交易記錄

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYR6yyfzbAAGs2hRlJFs866.png" height="484" />

瀏覽到儲存的交易記錄檔—DB2.trn

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M01/8E/EF/wKiom1jPYSCRmJKqAAGJDlmXzwM198.png" height="471" />

然後我們需要選擇刪除資料的日期及時間：我們上面記載了刪除資料的日期及時間 21:36

此時是恢複資料的主要關鍵地方。

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYSKS5r4jAAGBl1sBjus494.png" height="421" />

確認的時間點資訊

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M02/8E/EF/wKiom1jPYSPy7hOSAAGBdHo-H78410.png" height="415" />

我們此時需要單擊選項菜單：

恢複狀態：復原未提交的事務，使用資料庫處理可以使用的狀態。無法還原其他交易記錄(RESTORE WITH RECOVERY)

650) this.width=650;" title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="image" src="http://s3.51cto.com/wyfs02/M02/8E/ED/wKioL1jPYSTi0WY5AADYxNNsBog495.png" height="367" />

我們也可以通過指令碼進行資料還原。

RESTORE LOG [DB2] FROM DISK = N‘D:\DB_BACKUP\DB.trn‘ WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N‘2017-03-19T21:36:01‘GO

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M00/8E/ED/wKioL1jPYSXy23mPAAGLiKj7_i8084.png" height="418" />

還原成功後，資料庫狀態顯示正常

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M01/8E/ED/wKioL1jPYSaxAHfIAAF8PWlTI4g134.png" height="457" />

接下來我們查看資料，恢複正常了；

650) this.width=650;" title="clipboard" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px;" border="0" alt="clipboard" src="http://s3.51cto.com/wyfs02/M01/8E/ED/wKioL1jPYSiRHGbJAAFpZaJSKYk223.png" height="438" />

本文出自 “高文龍” 部落格，謝絕轉載！

SQL Server 資料恢複到指點時間點(完整恢複)