SQL Server 備份-結尾交易紀錄檔

沿續上一章的備份

最後有提到所謂的結尾交易紀錄檔
也就是「距離資料庫最後一次任何備份」至「現在」的紀錄
這是在FULL及BULK_LOGGED的復原模式才會存在

上章有舉個例子：
一、每個月做一次完整備份，每周日做差異或LOG備份
二、假設今天在第2個禮拜三，資料庫中某MDF或NDF硬碟掛掉了

現在還原了完整備份及差異備份
那請問禮拜一到禮拜三的紀錄，怎麼辦???



這時在sql server 2005後就出現了防呆機制
它會主動查看LOG檔是否還存在
如果還在就不會讓使用者RESTORE指令通過
避免使用者在還原時沒有還原到：
「最後一次備份」至「現在」之間的資料差異
這也就是所謂的結尾交易紀錄

當然還有個問題，如果連LOG檔都不見了呢???
那就無解了，系統查無LOG檔，那就只會還原到第二個禮拜日
而禮拜一至禮拜三就消失。

不過基本上LOG檔通常會使用RAID10或01的方式另外存放
整個系統都掛了沒關係，只要LOG檔還在通常都會救得回來。
所以LOG的磁碟通常比較好，也有可能會異地存放。

差異備份程式碼如下：

--完整備份
BACKUP DATABASE 中文北風 TO DISK='C:\backup\北風完整.BAK'

--塞點資料，產生差異
SELECT * INTO 中文北風.dbo.新供應商  FROM 中文北風.dbo.供應商 

--第1次差異備份
BACKUP DATABASE 中文北風 TO DISK='C:\backup\北風差1.BAK' WITH DIFFERENTIAL

--塞點資料，產生差異
SELECT * INTO 中文北風.dbo.新客戶  FROM 中文北風.dbo.客戶 

--第2次差異備份
BACKUP DATABASE 中文北風 TO DISK='C:\backup\北風差2.BAK' WITH DIFFERENTIAL

--塞點資料，產生差異
SELECT * INTO 中文北風.dbo.新產品資料  FROM 中文北風.dbo.產品資料

--此時資料庫毀損，模擬中文北風的MDF檔不見了
USE master
GO

--將中文北風離線，並到資料庫位置刪除實際檔案
--這時還原完整的會出錯，因為系統偵測到log檔的存在
--會強制要求備份結尾交易紀錄
--WITH NO_TRUNCATE 不截斷交易紀錄 
--NORECOVERY 之後馬上要還原了，所以先將資料庫卡住
BACKUP LOG 中文北風 TO DISK='C:\backup\北風尾.BAK' WITH NO_TRUNCATE,NORECOVERY

--逐一還原
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風完整.BAK' WITH NORECOVERY
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風差2.BAK' WITH NORECOVERY
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風尾.BAK' WITH RECOVERY

LOG備份程式碼如下：

--先試做LOG的備份及還原，跟DIFFERENTIAL很像
--完整備份
BACKUP DATABASE 中文北風 TO DISK='C:\backup\北風完整.BAK'

--塞點資料，產生差異
SELECT * INTO 中文北風.dbo.新供應商  FROM 中文北風.dbo.供應商 

--第一次備份log檔，不用加WITH DIFFERENTIAL
BACKUP LOG 中文北風 TO DISK='C:\backup\北風LOG1.BAK'

--塞點資料，產生差異
SELECT * INTO 中文北風.dbo.新客戶  FROM 中文北風.dbo.客戶 

--第二次備份log檔
BACKUP LOG 中文北風 TO DISK='C:\backup\北風LOG2.BAK'

--塞點資料，產生差異
SELECT * INTO 中文北風.dbo.新產品資料  FROM 中文北風.dbo.產品資料

--將中文北風離線，並刪除實際檔案
USE master
GO

--備份結尾交易紀錄，並準備還原
BACKUP LOG 中文北風 TO DISK='C:\backup\北風尾.BAK' WITH NO_TRUNCATE,NORECOVERY

--log檔的還原全部都要在，不能缺少任何一個
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風完整.BAK' WITH NORECOVERY
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風LOG1.BAK' WITH NORECOVERY
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風LOG2.BAK' WITH NORECOVERY
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風尾.BAK' WITH RECOVERY

現在有個別的問題，假設今天資料庫某個表出錯了
而資料庫是好的，像是人為誤刪等等
那這時可以先把備份檔另外掛上Server，並指定存放位置
再把資料表到回原本的資料庫，也是種解決方式：

BACKUP DATABASE 中文北風 TO DISK='C:\backup\北風完整.BAK'

--人為的誤刪
DELETE FROM 中文北風.DBO.客戶
--所有資料都不見了 
SELECT * FROM 中文北風.DBO.客戶

--現在要回復其中的客戶表
--先還原出整個資料庫叫「暫用資料庫」，再將資料倒回原本的資料庫

--查查有什麼資料庫
RESTORE HEADERONLY FROM DISK='C:\backup\北風完整.BAK'
--查查資料庫中的檔
RESTORE FILELISTONLY FROM DISK='C:\backup\北風完整.BAK'

--還原時提醒搬家位置，每個MDF、LDF、NDF都要指定位置
RESTORE DATABASE 中文北風暫用 FROM DISK='C:\backup\北風完整.BAK' 
 WITH RECOVERY,
  MOVE '中文北風' TO 'C:\Data\中文北風AAA.mdf',
  MOVE '中文北風_log' TO 'C:\Data\中文北風bbb.ldf'
GO

--塞回去
INSERT INTO 中文北風.dbo.客戶
  SELECT * FROM 中文北風暫用.DBO.客戶

--刪掉暫用的資料庫
DROP DATABASE 中文北風暫用

而除上面的方法，LOG檔還可以還原到指定時間點：

BACKUP DATABASE 中文北風 TO DISK='C:\backup\北風完整.BAK'

--模擬資料差異
SELECT * INTO 中文北風.dbo.新客戶 FROM 中文北風.dbo.客戶
SELECT * INTO 中文北風.dbo.新供應商 FROM 中文北風.dbo.供應商


--模擬人為錯誤操作  --21:24:30
DELETE FROM 中文北風.dbo.員工
DELETE TOP(50) PERCENT FROM 中文北風.dbo.訂貨主檔
DROP TABLE 中文北風.dbo.產品資料
UPDATE 中文北風.dbo.客戶 SET 郵遞區號='10000'


--交易記錄檔備份
ALTER DATABASE 中文北風 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
BACKUP LOG 中文北風 TO DISK='C:\backup\北風LOG.BAK' WITH NORECOVERY


--利用交易記錄檔還原資料庫至指定的時間點----
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風完整.BAK' WITH NORECOVERY
RESTORE DATABASE 中文北風 FROM DISK='C:\backup\北風LOG.BAK'
 WITH RECOVERY,STOPAT='2014-6-13 21:24:30'

ALTER DATABASE 中文北風 SET MULTI_USER