来自 新葡亰数据库 2019-11-12 13:32 的文章
当前位置: 澳门新葡亰app > 新葡亰数据库 > 正文

精心检查后开掘完整备份存在,后边防检查查错

几日前高出一齐有关"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."的案例。

 

现身难点的时候,小编去奉行三个特简单的SQL语句,实践时间十分短,检查未有堵塞。符合规律情形下,应该是几秒就OK。前面检查错误日志,发掘成雅量那类信息.而这一个点,我们一直不备份数据库的作业。前边搜索,了然了大器晚成晃这一个新闻出现的因由:

最近生机勃勃台SQL Server服务器安排SQL Server Backup后,发现每晚的反差备份老是退步,报如下错误:

 

 

澳门新葡亰app 1

Msg 3035, Level 16, State 1, Line 1

 

无法实践数据库"xxxx" 的异样备份,因为官样文章当前数据库备份。请去掉WITH DIFFERENTIAL 选项后再也发出BACKUP DATABASE 以奉行数据库的欧洲经济共同体备份。

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

Msg 3013, Level 16, State 1, Line 1

 

BACKUP DATABASE 正在相当终止。

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.

 

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.

现身那么些错误,平日是因为未有做过完整备份或备份链中断(chain of backups to break卡塔 尔(英语:State of Qatar),稳重检查后意识完全备份存在,那么就大概是备份链中断所致,检查备份日志记录:

 

 

翻译如下:

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错误日志的应用程序。

 

澳门新葡亰app,那么那几个音信是怎么样意思吧? 让自家用三个例子来分解下。 要是你布署ntbackup.exe去备份D盘。那么些磁盘上有一点点SQL Server的数据库相关的数据文件。由于SQL Server要动用那个数据文件,由此豆蔻梢头旦那个文件在备份时复制将现出区别等。为了保障数据库文件在磁盘备份时是同等的,这几个应用程序内部会利用BACKUP DATABASE [databasename] WITH SNAPSHOT命令来备份数据库。当命令奉行时,数据库上的I/O会冻结况统筹份应用程序被公告继续扩充起操作。直到BACKUP WITH SNAPSHOT命令试行到位,数据库的冻结的I/O当备份命令豆蔻梢头旦产生就能回复。相应的新闻也就能记录到SQL Server错误日志中。

 

 

澳门新葡亰app 2

后边检查发掘,刚刚我们在此个时刻段有PlateSpin的备份作业在运作(数据库服务器是VMware,系统管理员用PlateSpin做DCRUISER卡塔 尔(阿拉伯语:قطر‎。所以也是不当日志现身那一个消息的由来。 其它,关于那么些知识点,也许有上边一些资料供参照他事他说加以考查、学习。

 

 

澳门新葡亰app 3

案例Frozen messages while taking NT Backup for SQL databases

 

 

察觉备份日志里面有一条记下将数据库备份到Virtual Device,其实前面在这里篇小说“IO is frozen on database xxx, No user action is required澳门新葡亰官网APP,”里面介绍过是因为PlateSpin的备份作业在运作(数据库服务器是VMware,系统管理员用PlateSpin做DLacrosse,使用了卷影复制(Volume Shadow Copy VSS)。

关于VDI(VSS)的介绍,能够参照上面链接

 

How It Works: SQL Server – VDI (VSS) Backup Resources

除此以外,查了风度翩翩晃,蒙受此信息是还应该有不小概率是因为在利用此外备份实施方案(NTBackup,BE,Bacula等卡塔尔国,它们会对数据库举行形似快速照相的备份(VSS别本

 

  • 卷影复制 Volume Shadow Copy卡塔 尔(英语:State of Qatar)。 通过对数据库创制备份快速照相(creating snapshots backups卡塔尔,大概会产生备份链中断,并使原先的歧异备份或完整备份无效,那正是为什么在您做差距备份(DIFFERENTIAL BACKUP卡塔 尔(阿拉伯语:قطر‎的长河中相见这些荒谬的来由,因为备份链中断了,备份链无效( backup chain invalid卡塔尔。在出入备份前需求先做叁个整机备份。

此外关于Database Snapshots (SQL Server)它也许有生龙活虎对限量和质量成本的。如下截图所示:

 

 

除此以外,碰到这几个张冠李戴,还可能有比很大可能率有别的二种原因:

澳门新葡亰app 4

 

 

1:修改了恢复生机情势( Recovery Model卡塔尔,因为将数据库的恢复生机形式改善为简易格局也会招致备份链中断。

仿照效法资料:

 

2:特定版本的Bug,这么些只见到于SQL Server 二〇〇六特定版本,具体参谋 经常超少见,只是寻找时,开掘存这几个情状,所以选定在这里。

 

 

寸草不留方案:

 

    能够禁止使用SQL Server VSS Writer服务,来阻拦那多少个备份建设方案使用卷影复制,测验开掘不会影响PlateSpin的功课。

 

 

 

本文由澳门新葡亰app发布于新葡亰数据库,转载请注明出处:精心检查后开掘完整备份存在,后边防检查查错

关键词: