SQLServer異常故障恢復(二)

SQLServer 異常故障恢復

當 SQLServer 檔案損壞或例項出現故障，一般需要透過備份來恢復資料，除了恢復時間外，最重要的還是資料丟失情況，零丟失資料是最理想的情況，除了日常資料庫完整備份，日誌備份以外，在出現故障時還需要進行事物日誌尾部日誌的備份，將增量資料完全備份出來，才能進行完全恢復；

環境 : SQLServer2012

OS:Windows 7

一：例項可以啟動情況下，進行恢復

1. 建立測試資料庫

2. 完整備份資料庫

3. 插入資料

4. 備份日誌

5. 插入資料

6. 備份日誌

7. 模擬故障 ( 停止 SQLServer 服務 ， 重新命名 mdf )

8. 啟動資料庫例項，事務日誌尾部備份

9. 資料庫恢復

10. 驗證資料

 

二：例項無法啟動情況下，進行恢復

1. 建立測試資料庫

2. 完整備份資料庫

3. 插入資料

4. 備份日誌

5. 插入資料

6. 備份日誌

7. 模擬故障 ( 停止 SQLServer 服務 ， 並假設例項無法啟動 )

8. 資料庫例項無法啟動，複製資料完整備份，日誌備份，對應日誌檔案到其他伺服器上

9. 新伺服器上建立同名資料庫，並設定離線

10. 重新命名新伺服器上新資料庫 mdf,lnf 檔案

11. 將舊庫複製過來的日誌檔案複製到新資料庫日誌目錄下

12. 透過新資料庫，備份老資料庫的事務日誌尾部

13. 備份成功後就可以透過老資料庫備份，老資料庫日誌備份，新資料庫產生的老事務日誌尾部備份完全恢復資料庫

14. 驗證資料

實驗過程參考 ：

https://www.cnblogs.com/mc67/p/4860338.html

http://www.cnblogs.com/CareySon/archive/2012/02/23/2365006.html

 

一：例項可以啟動情況下，進行恢復

實驗過程如下 ：

--- 建立測試資料 chenjch0930

CREATE DATABASE chenjch0930

GO

USE chenjch0930

GO

--- 建立測試表 test01

CREATE TABLE test01 ( id int , t_status varchar (100 ))

GO

--- 完整覆蓋壓縮備份資料庫

BACKUP DATABASE chenjch0930 TO DISK = 'F:\backup\chenjch20180930_FULL.bak' WITH compression , init

GO

--- 插入資料

INSERT INTO test01 VALUES (1 , ' 完整備份後插入的資料 1' )

INSERT INTO test01 VALUES (2 , ' 完整備份後插入的資料 2' )

INSERT INTO test01 VALUES (3 , ' 完整備份後插入的資料 3' )

GO

-- 備份事務日誌

BACKUP LOG chenjch0930 TO DISK = 'F:\backup\chenjch20180930a_LOG.trn'

GO

-- 再次插入資料

INSERT INTO test01 VALUES (4 , ' 日誌備份後插入的資料 4' )

INSERT INTO test01 VALUES (5 , ' 日誌備份後插入的資料 5' )

GO

-- 備份事務日誌

BACKUP LOG chenjch0930 TO DISK = 'F:\backup\chenjch20180930b_LOG.trn'

GO

-- 再次插入資料 ( 插入後沒有進行日誌備份 )

INSERT INTO test01 VALUES (6 , ' 日誌備份後插入的資料 6' )

INSERT INTO test01 VALUES (7 , ' 日誌備份後插入的資料 7' )

INSERT INTO test01 VALUES (8 , ' 日誌備份後插入的資料 8' )

GO

--- 檢視資料

select count (*) from test01 ;

select database_id , file_id , name , physical_name , size from sys . master_files where database_id = '12' ;

database_id file_id name    physical_name   size

12  1   chenjch0930 D:\ sqlserver2012\ data\ master\ chenjch0930 . mdf    520

12  2   chenjch0930_log D:\ sqlserver2012\ data\ master\ chenjch0930_log . ldf    130

 

--- 停止 SQLSERVER 例項服務

--- 將 D:\sqlserver2012\data\master\chenjch0930.mdf 重新命名為 D:\sqlserver2012\data\master\chenjch0930.mdf.bak

--- 啟動 SQLSERVER 例項服務

D: \ sqlserver2012\0\ MSSQL11 . MSSQLSERVER\ MSSQL\ Log\ ERRORLOG

2018 -09 -30 15 :36 :08.18 spid25s     Error: 17204 , Severity: 16 , State: 1.

2018 -09 -30 15 :36 :08.18 spid25s     FCB :: Open failed: Could not open file D:\ sqlserver2012\ data\ master\ chenjch0930 . mdf for file number 1.  OS error: 2 ( 系統找不到指定的檔案 。 ).

2018 -09 -30 15 :36 :08.38 spid25s     Error: 5120 , Severity: 16 , State: 101.

2018 -09 -30 15 :36 :08.38 spid25s     Unable to open the physical file "D:\sqlserver2012\data\master\chenjch0930.mdf" . Operating system error 2 : "2( 系統找不到指定的檔案。 )" .

--- 雖然有報錯，但是例項還是可以啟動

---chenjch0930 顯示 " 恢復掛起 " 狀態

 

--- 備份尾部日誌

--- 如果不備份尾部日誌，只透過現有的資料庫和日誌備份恢復會丟失 3 條資料

USE master

GO

BACKUP LOG chenjch0930 TO DISK = 'F:\backup\chenjch20180930c_tail_LOG.trn'

WITH INIT , NO_TRUNCATE   -----NO_TRUNCATE 該選項允許在資料庫損壞時備份日誌

GO

--- 如果指定 NO_TRUNCATE 選項，如有如下報錯：

--- 訊息 945 ，級別 14 ，狀態 2 ，第 1 行

--- 由於檔案不可訪問，或者記憶體或磁碟空間不足，所以無法開啟資料庫 'chenjch0930' 。有關詳細資訊，請參閱 SQL Server 錯誤日誌。

--- 訊息 3013 ，級別 16 ，狀態 1 ，第 1 行

---BACKUP LOG 正在異常終止。

 

--- 然後依次恢復

restore filelistonly from disk = 'F:\backup\chenjch20180930_FULL.bak' ;

restore filelistonly from disk = 'F:\backup\chenjch20180930a_LOG.trn' ;

restore filelistonly from disk = 'F:\backup\chenjch20180930b_LOG.trn' ;

restore filelistonly from disk = 'F:\backup\chenjch20180930c_tail_LOG.trn' ;

RESTORE DATABASE chenjch0930_NEW

FROM DISK = 'F:\backup\chenjch20180930_FULL.bak'

WITH NORECOVERY ,

MOVE 'chenjch0930' TO 'D:\data\chenjch0930.MDF' ,

MOVE 'chenjch0930_LOG' TO  'D:\data\chenjch0930_LOG.LDF'

GO

RESTORE LOG chenjch0930_NEW

FROM DISK = 'F:\backup\chenjch20180930a_LOG.trn'

WITH NORECOVERY

GO

RESTORE LOG chenjch0930_NEW

FROM DISK = 'F:\backup\chenjch20180930b_LOG.trn'

WITH NORECOVERY

GO

RESTORE LOG chenjch0930_NEW

FROM DISK = 'F:\backup\chenjch20180930c_tail_LOG.trn'

WITH RECOVERY

--- 測試

--- 沒有丟失資料

USE chenjch0930_NEW

GO

SELECT count (*) FROM test01 ;  ---8

---drop database chenjch0930;

 

二：例項無法啟動情況下，進行恢復

--- 建立測試資料 chenjch0930

---use master

---drop database chenjch0930_NEW

CREATE DATABASE chenjch0930

GO

USE chenjch0930

GO

--- 建立測試表 test01

CREATE TABLE test01 ( id int , t_status varchar (100 ))

GO

--- 完整覆蓋壓縮備份資料庫

BACKUP DATABASE chenjch0930 TO DISK = 'F:\backup\chenjch20180930_FULL.bak' WITH compression , init

GO

--- 插入資料

INSERT INTO test01 VALUES (1 , ' 完整備份後插入的資料 1' )

INSERT INTO test01 VALUES (2 , ' 完整備份後插入的資料 2' )

INSERT INTO test01 VALUES (3 , ' 完整備份後插入的資料 3' )

GO

-- 備份事務日誌

BACKUP LOG chenjch0930 TO DISK = 'F:\backup\chenjch20180930a_LOG.trn'

GO

-- 再次插入資料

INSERT INTO test01 VALUES (4 , ' 日誌備份後插入的資料 4' )

INSERT INTO test01 VALUES (5 , ' 日誌備份後插入的資料 5' )

GO

-- 備份事務日誌

BACKUP LOG chenjch0930 TO DISK = 'F:\backup\chenjch20180930b_LOG.trn'

GO

-- 再次插入資料 ( 插入後沒有進行日誌備份 )

INSERT INTO test01 VALUES (6 , ' 日誌備份後插入的資料 6' )

INSERT INTO test01 VALUES (7 , ' 日誌備份後插入的資料 7' )

INSERT INTO test01 VALUES (8 , ' 日誌備份後插入的資料 8' )

GO

--- 檢視資料

select count (*) from test01 ;

select database_id , file_id , name , physical_name , size from sys . master_files where database_id = '12' ;

database_id file_id name    physical_name   size

12  1   chenjch0930 D:\ sqlserver2012\ data\ master\ chenjch0930 . mdf    520

12  2   chenjch0930_log D:\ sqlserver2012\ data\ master\ chenjch0930_log . ldf    130

複製

D: \ sqlserver2012\ data\ master\ chenjch0930_log . ldf

F:\backup\chenjch20180930_FULL.bak

F:\backup\chenjch20180930a_LOG.trn

F:\backup\chenjch20180930b_LOG.trn

到新伺服器 D:\backup\chen0913\backup0930 目錄下

 

--- 建立相同名稱的資料庫，並設定為離線

CREATE DATABASE chenjch0930 ;

ALTER DATABASE chenjch0930 SET OFFLINE  WITH ROLLBACK IMMEDIATE ;

--- 檢視檔案位置

select * from sys . master_files ;

D: \ Microsoft SQL Server\2\ MSSQL11 . MSSQLSERVER\ MSSQL\ DATA\ chenjch0930_log . ldf

D: \ Microsoft SQL Server\2\ MSSQL11 . MSSQLSERVER\ MSSQL\ DATA\ chenjch0930 . mdf

--- 重新命名 D:\Microsoft SQL Server\2\MSSQL11.MSSQLSERVER\MSSQL\DATA\chenjch0930_log.ldf 為 chenjch0930_log.ldf.bak

--- 重新命名 D:\Microsoft SQL Server\2\MSSQL11.MSSQLSERVER\MSSQL\DATA\chenjch0930.mdf 為 chenjch0930.mdf.bak 

--- 將之前的複製過來的日誌檔案 chenjch0930_log.ldf 複製到新資料庫日誌目錄下

--- 透過新資料庫，備份老資料庫的事務日誌尾部

USE master

GO

BACKUP LOG chenjch0930 TO DISK = 'D:\backup\chen0913\backup0930\chenjch20180930c_tail_LOG.trn'

WITH INIT , NO_TRUNCATE   -----NO_TRUNCATE 該選項允許在資料庫損壞時備份日誌

GO

--- 備份成功後就可以透過老資料庫備份，老資料庫日誌備份，新資料庫產生的老事務日誌尾部備份完全恢復資料庫

--- 然後依次恢復

restore filelistonly from disk = 'D:\backup\chen0913\backup0930\chenjch20180930_FULL.bak' ;

restore filelistonly from disk = 'D:\backup\chen0913\backup0930\chenjch20180930a_LOG.trn' ;

restore filelistonly from disk = 'D:\backup\chen0913\backup0930\chenjch20180930b_LOG.trn' ;

restore filelistonly from disk = 'D:\backup\chen0913\backup0930\chenjch20180930c_tail_LOG.trn' ;

RESTORE DATABASE chenjch0930_NEW

FROM DISK = 'D:\backup\chen0913\backup0930\chenjch20180930_FULL.bak'

WITH NORECOVERY ,

MOVE 'chenjch0930' TO 'D:\data\0913\chenjch0930.MDF' ,

MOVE 'chenjch0930_LOG' TO  'D:\data\0913\chenjch0930_LOG.LDF'

GO

RESTORE LOG chenjch0930_NEW

FROM DISK = 'D:\backup\chen0913\backup0930\chenjch20180930a_LOG.trn'

WITH NORECOVERY

GO

RESTORE LOG chenjch0930_NEW

FROM DISK = 'D:\backup\chen0913\backup0930\chenjch20180930b_LOG.trn'

WITH NORECOVERY

GO

RESTORE LOG chenjch0930_NEW

FROM DISK = 'D:\backup\chen0913\backup0930\chenjch20180930c_tail_LOG.trn'

WITH RECOVERY ;

 

--- 測試

--- 沒有丟失資料

USE chenjch0930_NEW

GO

SELECT count (*) FROM test01 ;  ---8

---drop database chenjch0930;