澳门金沙vip 1

SQL Server误区30日谈 第20天 破坏日志备份链之后,需要一个完整备份来重新开始日志链

澳门金沙vip, 

最近遇到一起关于”I/O is frozen on database xxx. No user action is
required. However, if I/O is not resumed promptly, you could cancel the
backup.”的案例。

误区 #20:在破坏日志备份链之后,需要一个完整备份来重新开始日志链
错误

最近一台SQL
Server服务器部署SQL Server
Backup后,发现每晚的差异备份老是失败,报如下错误:

出现问题的时候,我去执行一个非常简单的SQL语句,执行时间非常长,检查没有阻塞。正常情况下,应该是几秒就OK。后面检查错误日志,发现有大量这类消息.而这个点,我们没有备份数据库的作业。后面搜索,了解了一下这个消息出现的原因:

事务日志备份会备份自上次事务日志备份以来所有的事务日志(如果从来没有过日志备份的话,那就从上一次完整备份开始)。有好几种类型的操作会中断事务日志的连续性,也就是说除非重新开始新的日志链,SQL
Server无法再进行日志备份。下面这几种操作都有可能引起日志链断裂:

 

 

由完整恢复模式或大容量事务日志恢复模式转为简单恢复模式
从数据库镜像进行恢复
备份日志时指定了NO_LOG 或 WITH TRUNCATE_ONLY(还好在SQL Server
2008中这个选项被取消了)

Msg
3035, Level 16, State 1, Line 1

澳门金沙vip 1

更多请看:post BACKUP LOG WITH NO_LOG – use, abuse, and undocumented
trace flags to stop
it

无法执行数据库”xxxx”
的差异备份,因为不存在当前数据库备份。请去掉WITH DIFFERENTIAL
选项后重新发出BACKUP DATABASE 以执行数据库的完整备份。

 

通过下面的例子对此进行阐述:

Msg
3013, Level 16, State 1, Line 1

参考网上资料,关于“I/O is frozen on database xxx. No user action is
required”的介绍如下:

复制代码 代码如下:

BACKUP
DATABASE 正在异常终止。

 

CREATE DATABASE LogChainTest;
GO
ALTER DATABASE LogChainTest SET RECOVERY FULL;
GO
BACKUP DATABASE LogChainTest TO DISK = ‘C:\SQLskills\LogChainTest.bck’
WITH INIT;
GO
BACKUP LOG LogChainTest TO DISK =
‘C:\SQLskills\LogChainTest_log1.bck’ WITH INIT;
GO
ALTER DATABASE LogChainTest SET RECOVERY SIMPLE;
GO
ALTER DATABASE LogChainTest SET RECOVERY FULL;
GO

 

This message is logged in the Error Log whenever any backup service
making use of SQL Server Virtual Device Interface (VDI) tries to backup
the database (with snapshot)/drive on which the database files reside.
Microsoft Backup (ntbackup.exe), Volume Shadow Copy (VSS), Data
Protection Manager (DPM) and third party tools like Symantec Business
Continuance Volume (BCV) are some of the application which cause this
message to logged in the SQL Server Error Log.

结果是:

出现这个错误,一般是因为没有做过完整备份或备份链中断(chain
of backups to
break),仔细检查后发现完整备份存在,那么就可能是备份链中断所致,检查备份日志记录:

What does these messages mean? Let me explain this with an example.
Suppose ntbackup.exe is configured to take the backup of D drive. This
drive has some data files related to few databases on SQL Server. Since
the data files are in use by SQL Server, if these files are copied as it
is the files in the backup will be inconsistent. To ensure that the
database files are consistent in the drive backup, this application
internally issues a BACKUP DATABASE [databasename] WITH SNAPSHOT
command against the database. When this command is issued, the I/O for
that database is frozen and the backup application is informed to
proceed with its operation. Until the BACKUP WITH SNAPSHOT command is
complete, the I/O for the database is frozen and the I/O is resumed once
it completes. The corresponding messages are logged in the SQL Server
Error Log.

复制代码 代码如下:

 

 

已为数据库 ‘LogChainTest’,文件 ‘LogChainTest’ (位于文件 1 上)处理了 168
页。
已为数据库 ‘LogChainTest’,文件 ‘LogChainTest_log’ (位于文件 1
上)处理了 2 页。
BACKUP DATABASE 成功处理了 170 页,花费 0.224 秒(5.916 MB/秒)。
已为数据库 ‘LogChainTest’,文件 ‘LogChainTest_log’ (位于文件 1
上)处理了 3 页。
BACKUP LOG 成功处理了 3 页,花费 0.121 秒(0.137 MB/秒)。

SELECT  CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS server_name ,

        bs.database_name ,

        bs.backup_start_date ,

        bs.backup_finish_date ,

        bs.expiration_date ,

        CASE bs.type

          WHEN 'D' THEN 'Full Backup'

          WHEN 'I' THEN 'Diff Backup'

          WHEN 'L' THEN 'Log  Bacup'

          WHEN 'F' THEN 'File Backup'

          WHEN 'G' THEN 'File Diff'

          WHEN 'P' THEN 'Partial Backup'

          WHEN 'Q' THEN 'Partial Diff Backup'

        END AS backup_type ,

        CASE bf.device_type 

          WHEN 2 THEN 'Disk'

          WHEN 5 THEN 'Tape'

          WHEN 7 THEN 'Virtual Device'

          WHEN 105 THEN 'permanent backup device'

        END AS backup_media,

        bs.backup_size/1024/1024/1024  AS [backup_size(GB)] ,

        bs.compressed_backup_size/1024/1024/1024 AS [compressed_backup_size(GB)],

        bf.logical_device_name ,

        bf.physical_device_name ,

        bs.name AS backupset_name ,

        bs.first_lsn,

        bs.last_lsn,

        bs.checkpoint_lsn,

        bs.description

FROM    msdb.dbo.backupmediafamily bf

        INNER JOIN msdb.dbo.backupset bs ON  bf.media_set_id = bs.media_set_id

WHERE bs.database_name='databasename'

ORDER BY  bs.backup_start_date DESC;

翻译如下:

我首先创建了一个数据库,将其设置为完整恢复模式,这个是日志链的起点,然后转为简单恢复模式,再转为完整恢复模式。
下面我再尝试进行日志备份

 

当任何备份服务利用SQL Server虚拟设备接口(VDI)尝试备份数据库(使用with
snapshot时)或数据库文件所在的磁盘时,这个消息就会记录在错误日志(Error
Log)里。 Micorsoft Backup(ntbackup.exe),卷影复制(Volume Shadow Copy
VSS), 数据保护管理器(Data Protection Manager
DPM)和第三方工具,例如赛门铁克Symantec 业务连续性卷(Business
Continuance Volume)(BCV),这些都是会导致这类消息记录到SQL
Server错误日志的应用程序。

复制代码 代码如下: