Sql Server利用日誌恢複資料庫

Sql Server利用日誌恢複資料庫

要有一個完全備份+加上當前最新的記錄備份才行

 

*--說明：
將主要資料庫中的資料變化及時反饋到備用資料庫中
備用資料庫的資料可以隨時用於查詢，但不能被更新（備用資料庫唯讀）。
--*/

--首先,建立一個示範用的資料庫(主要資料庫)

1. CREATE DATABASE Db_test  
2. ON  
3. ( NAME = Db_test_DATA,  
4.       FILENAME = 'c:/Db_test.mdf' )  
5. LOG ON  
6. ( NAME = Db_test_LOG,  
7.    FILENAME = 'c:/Db_test.ldf')  
8. GO  

--對資料庫進行備份

1. BACKUP DATABASE Db_test TO DISK='c:/test_data.bak' WITH FORMAT  
2. GO  

--把資料庫還原成備用資料庫(示範主要資料庫與這個備用資料庫之間的同步)

1. RESTORE DATABASE Db_test_bak FROM DISK='c:/test_data.bak'  
2. WITH REPLACE,STANDBY='c:/db_test_bak.ldf'  
3. ,MOVE 'Db_test_DATA' TO 'c:/Db_test_data.mdf'  
4. ,MOVE 'Db_test_LOG' TO 'c:/Db_test_log.ldf'  
5. GO  

--啟動 SQL Agent 服務

1. EXEC master..xp_cmdshell 'net start sqlserveragent',no_output  
2. GO  

--建立主伺服器資料訓與待命伺服器資料庫之間同步的作業

1. DECLARE @jogid uniqueidentifier  
2. EXEC msdb..sp_add_job  
3. @job_id = @jogid OUTPUT,  
4. @job_name = N'資料同步處理'  

--建立同步處理步驟

1. EXEC msdb..sp_add_jobstep  
2. @job_id = @jogid,  
3. @step_name = N'資料同步',  
4. @subsystem = 'TSQL',  
5. @command = N'  

--主要資料庫中進行記錄備份

1. BACKUP LOG Db_test TO DISK=''c:/test_log.bak'' WITH FORMAT  

--備用資料庫中還原主要資料庫的記錄備份(應用主要資料庫中的最新變化
--實際應該時主要資料庫備份與備用資料庫的還原作業應該分別在主伺服器和待命伺服器上建立，並且備份檔案應該放在主伺服器和備用都能訪問的共用目錄中

1. RESTORE LOG Db_test_bak FROM DISK=''c:/test_log.bak'' WITH STANDBY=''c:/test_log.ldf''',  
2. @retry_attempts = 5,  
3. @retry_interval = 5  

--建立調度(每分鐘執行一次)

1. EXEC msdb..sp_add_jobschedule  
2. @job_id = @jogid,  
3. @name = N'時間安排',  
4. @freq_type=4,  
5. @freq_interval=1,  
6. @freq_subday_type=0x4,  
7. @freq_subday_interval=1,  
8. @freq_recurrence_factor=1  

-- 添加目標伺服器

1. EXEC msdb.dbo.sp_add_jobserver  
2. @job_id = @jogid,  
3. @server_name = N'(local)'  
4. GO  

--通過上述處理,主要資料庫與備用資料庫之間的同步關係已經設定完成

下面開始測試是否能實現同步

 

--在主要資料庫中建立一個測試用的表

1. CREATE TABLE Db_test.dbo.TB_test(ID int)  
2. GO  

--等待1分鐘30秒（由於同步的時間間隔設定為1分鐘,所以要延時才能看到效果）

1. WAITFOR DELAY '00:01:30'  
2. GO  

--查詢一下備用資料庫,看看同步是否成功

1. SELECT * FROM Db_test_bak.dbo.TB_test  

/*--結果:
ID         
-----------

 

（所影響的行數為 0 行）
--*/

--測試成功
GO

--最後刪除所有的測試

1. DROP DATABASE Db_test,Db_test_bak  
2. EXEC msdb..sp_delete_job @job_name=N'資料同步處理'  
3. GO  

/*===========================================================*/

 

/*--伺服器檔機處理說明
使用這種方式建立的資料庫同步，當主要資料庫不可用時（例如，主要資料庫損壞或者停機檢修）
可以使用以下兩種方法使備用資料庫可用。
--*/

--1. 如果主要資料庫損壞，無法備份出最新的日誌，可以直接使用下面的語句使備用資料庫可讀寫（丟失最近一次日誌還原後的所有資料）。
--RESTORE LOG Db_test_bak WITH RECOVERY

--2. 如果主要資料庫可以備份出最新日誌，則可以使用下面的語句。
--先備份主要資料庫的最新的交易記錄
--BACKUP LOG Db_test TO DISK=''c:/test_log.bak'' WITH FORMAT
--再在備用資料庫中恢複最新的交易記錄，並且使備用資料庫可讀寫（升級為主要資料庫）
--RESTORE LOG Db_test_bak FROM DISK='c:/test_log.bak'

 

 

簡單地說:
1. 你的sql服務要使用指定的windows使用者登陸, 而不能使用"本地系統帳戶"
2. 用於登陸sql服務的使用者要求對共用目錄具有所有許可權
3. 如果你的電腦沒有加入到域, 還必須保證源和目標伺服器的sql服務設定的登陸使用者是一樣的(使用者名稱和密碼都一樣)

 

網路備份主要是使用權限設定問題, 參考下面的備份檔案共用目錄使用權限設定方法去解決目錄的共用許可權就可以了

下面假設是假設A伺服器上的Database Backup到B伺服器上的共用目錄使用權限設定(兩台伺服器應該在區域網路內,允許目錄共用訪問)::

1.機器A,B建立一個同名的windows使用者,使用者組設定為administrators,並設定相同的密碼,做為備份檔案夾檔案夾的有效訪問使用者,操作:
我的電腦
--控制台
--管理工具
--電腦管理
--使用者和組
--右鍵使用者
--建立使用者
--建立一個隸屬於administrator組的登陸windows的使用者

2.在B機器器上,建立一個共用目錄,做為備份檔案的存放目錄,操作:
我的電腦--D:/ 建立一個目錄,名為: BAK
--右鍵這個建立的目錄
--屬性--共用
--選擇"共用該檔案夾"
--通過"許可權"按紐來設定具體的使用者權限,保證第一步中建立的使用者具有對該檔案夾的所有許可權
--確定

3.設定 MSSQLSERVER 及 SQLSERVERAGENT 服務的啟動使用者
開始--程式--管理工具--服務
--右鍵 MSSQLSERVER
--屬性--登陸--選擇"此賬戶"
--輸入或者選擇第一步中建立的windows登入使用者名稱
--"密碼"中輸入該使用者的密碼
--確定
--同樣的方法設定 SQLSERVERAGENT

4.在A機器上完成對B機器BAK目錄的映射

5.查詢分析器中執行下面的語句,檢驗是否成功:
exec master..xp_cmdshell 'dir 映射的盤符'

6.A伺服器上做備份計劃

備忘:建立一個新的使用者只是為了讓MSSQLSERVER服務的啟動帳戶與共用目錄的有效訪問同名且密碼相同,這樣才能通過驗證(所以你也可以用其他有效使用者來代替,只需要滿足使用者名稱和密碼相同,並且擁有足夠的許可權)

 

 

 

看這個例子

SQL code--資料還原到指定時間點的處理樣本
--建立測試資料庫
CREATE DATABASE Db
GO

--對資料庫進行備份
BACKUP DATABASE Db TO DISK='c:/db.bak' WITH FORMAT
GO

--建立測試表
CREATE TABLE Db.dbo.TB_test(ID int)

--延時1秒鐘,再進行後面的操作(這是由於SQL Server的時間精度最大為百分之三秒,不延時的話,可能會導致還原到時間點的操作失敗)
WAITFOR DELAY '00:00:01'
GO

--假設我們現在誤操作刪除了 Db.dbo.TB_test 這個表
DROP TABLE Db.dbo.TB_test

--儲存刪除表的時間
SELECT dt=GETDATE() INTO #
GO

--在刪除操作後,發現不應該刪除表 Db.dbo.TB_test

--下面示範了如何恢複這個誤刪除的表 Db.dbo.TB_test

--首先,備份交易記錄(使用交易記錄才能還原到指定的時間點)
BACKUP LOG Db TO DISK='c:/db_log.bak' WITH FORMAT
GO

--接下來,我們要先還原完全備份(還原日誌必須在還原完全備份的基礎上進行)
RESTORE DATABASE Db FROM DISK='c:/db.bak' WITH REPLACE,NORECOVERY
GO

--將交易記錄還原到刪除操作前（這裡的時間對應上面的刪除時間，並比刪除時間略早
DECLARE @dt datetime
SELECT @dt=DATEADD(ms,-20,dt) FROM # --擷取比表被刪除的時間略早的時間
RESTORE LOG Db FROM DISK='c:/db_log.bak' WITH RECOVERY,STOPAT=@dt
GO

--查詢一下,看錶是否恢複
SELECT * FROM Db.dbo.TB_test

/*--結果:
ID         
-----------

（所影響的行數為 0 行）
--*/

--測試成功
GO

--最後刪除我們做的測試環境
DROP DATABASE Db
DROP TABLE #

如何利用日誌還原功能?