20180930-SQLServer异常故障恢复(二)

chenoracle

于 2024-12-08 21:21:56 发布

阅读量459

点赞数 4

CC 4.0 BY-SA版权

文章标签： sqlserver 数据库

本文链接：https://blog.youkuaiyun.com/chenoracle/article/details/144332158

在这里插入图片描述
当 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;

欢迎关注我的微信公众号"IT小Chen"

20180930-SQLServer异常故障恢复(二)

环境 :

实验过程参考 ：

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

二：实例无法启动情况下，进行恢复

实验过程参考：