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

https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms189621(v%3dsql.100)

 

一：实例可以启动情况下，进行恢复

实验过程如下 ：

--- 创建测试数据 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;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-2215364/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-2215364/