MISTECH 技術手抄本

MSSQL Server 2012 Restore 備份還原概念：

MSSQL Server 2012 Restore 在還原的過程中，有三個階段 (Phase)：

1. Data Copy：建立實體檔案，與回存資料

2. Redo：從 LOG File 回存所有已經確認 ( commit ) 的 Transaction

3. Undo：回覆 ( Roll Back ) 在還原時間點時未確認的 Transaction (交易)

至於 MSSQL Server 2012 Restore 還原概念，大致是以下列 4 個步驟，不過這 4 個步驟應該也是大部份資料庫系統採用的方法，像 Oracle DB 的 RMAN 也是如此。

• 1. 先嚐試執行 Tail-log 備份
• 2. 回存最近一次全備份
• 3. 回存最近一次的差異備份
• 4. 回存所有備份 LOG Fiile 裡的 Transaction ，再回存第一步驟所備份 tail-log 中的 Transaction

備份還原實作

首先建立一個目錄 C:\backups 作為備份的目錄，接下來的步驟則是模擬資料庫 Northwind 的檔案毁損時，資料庫重建的完整過程

--1.建立備份裝置 mybackup(backup device)
指令：
EXEC master.dbo.sp_addumpdevice  @devtype = N'disk', @logicalname = N'mybackup', @physicalname = N'C:\backups\mybackup.bak'

--2.檢查 Northwind資料庫是否為 Full Recovery mode
 

--3.執行完整資料庫備份

指令：
BACKUP DATABASE [Northwind] TO  [mybackup] WITH FORMAT, INIT,  NAME = N'Northwind-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

--4.建立資料表(log中有此資料表)

指令：
use Northwind
go
create table table1 (a int , b int)
go

--5.backup log(log備份且 table1被清除)
指令：
BACKUP LOG [Northwind] TO  [mybackup] WITH NOFORMAT, NOINIT,  NAME = N'Northwind-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

--6.新增資料(log中有 1 1 )
指令：
insert table1 values (1,1)

--7.backup log(log備份且 1 1被清除)
指令：
BACKUP LOG [Northwind] TO  [mybackup] WITH NOFORMAT, NOINIT,  NAME = N'Northwind-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

--8.新增資料(log中有 2 2 )
指令：
insert table1 values (2,2)

--破壞資料庫

1.stop sql service
2.rename C:\TSQL2K8DB\Northwind.mdf --> C:\TSQL2K8DB\Northwind.xxx
3.copy C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA 到上層目錄
4.start sql service
5.sql log查詢壞掉的檔案
6.檢查資料庫狀態，這時資料庫的狀態會變成 Recovery Pending

--7.tail log backup
a.device右鍵-->database-->
Database:Northwind
backup type:log
option-->backup the tail of ....

因為資料庫：Northwind 已被破壞，所以只能從 Backup Devices：mybackup 作 Tail-log 備份，這也是在 MSSQL Server 2012 Backup 備份 中建議用 Backup Devices 的好處之一。

指令：
BACKUP LOG [Northwind] TO  [mybackup] WITH  NO_TRUNCATE , NOFORMAT, NOINIT,  NAME = N'Northwind-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 10

整個備份完的記錄可以 Device 的 Media Contents 中查詢

 
b.檢查資料庫狀態，因為資料庫：Northwind 的資料檔名已被修改成 Northwind.mdf --> Northwind.xxx，重啟 MSSQL Server 伺服器後，MSSQL Server 檢查有問題，會將 Northwind 的狀態改為 Recovery Pending

 

c.選 Northwind,右鍵-->task-->restore-->database
指令：
RESTORE DATABASE [Northwind] FROM  DISK = N'C:\backups\mybackup.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [Northwind] FROM  DISK = N'C:\backups\mybackup.bak' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [Northwind] FROM  DISK = N'C:\backups\mybackup.bak' WITH  FILE = 3,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
RESTORE LOG [Northwind] FROM  DISK = N'C:\backups\mybackup.bak' WITH  FILE = 4,  NOUNLOAD,  STATS = 10
GO

若是用 SQL Server Management Studio 回存也是很方便操作