数据库还原,备份链中断导致差距备份报错案例

不久前遇上一齐有关"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."的案例。

 

数据库还原的操作,分两步举行:第一步,验证(verify)备份文件;第二步,根据备份计策还原数据库;

出现难题的时候,作者去实施三个特别简单的SQL语句,推行时间相当长,检查未有阻塞。不荒谬情况下,应该是几秒就OK。后边防检查查错误日志,发掘有雅量那类新闻.而以此点,大家一直不备份数据库的学业。后边找出,明白了一晃那么些信息出现的缘故:

近年一台SQL Server服务器布署SQL Server Backup后,开采每晚的差别备份老是退步,报如下错误:

参考《backup1:开首数据库备份》,备份战略是:

 

 

  • 14日贰回完整备份,一天三次差距备份,一钟头一遍事情日志备份
  • 数据/日志的历次备份都应用二个独自的备份文件,数据备份的恢弘名是 .bak,日志备份的恢宏名是.trn

图片 1

Msg 3035, Level 16, State 1, Line 1

一,验证(Verifiy)备份文件

 

力所不及施行数据库"xxxx" 的差距备份,因为不设有当前数据库备份。请去掉WITH DIFFERENTIAL 选项后再行发出BACKUP DATABASE 以实行数据库的完整备份。

1,查看备份文件的文件列表(Data File 和 Log File)

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

Msg 3013, Level 16, State 1, Line 1

由于,数据或日志的历次备份,都采用二个独立的备份文件,因而,在备份文件中,只有三个backup set,File选项是1,固然不钦命该File选项,默许值是1。

 

BACKUP DATABASE 正在非常终止。

RESTORE FILELISTONLY 
FROM disk = 'D:TestDBBackupFolderSitedb_bak4.bak'
--with file=1;

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.

 

在SQL Server中,一个备份文件能够积攒多少个backup set,每二个backup set都以多少或日志的三回备份(完整或差距备份),那意味着,一个备份文件能够存款和储蓄八个数据库备份。为了便于管理备份文件,提出,每一个备份都存款和储蓄到独门的备份文件中,那样,每个备份文件只存款和储蓄贰回备份。

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),留心检查后意识完全备份存在,那么就或然是备份链中断所致,检查备份日志记录:

回到的结果聚焦,有多个比较重大的字段:

 

 

  • LogicalName:文件的逻辑名称
  • PhysicalName:文件的情理名称,是文件在OS上的门道 文件名,例如,D:Program FilesMicrosoft SQL ServerMSSQLDataSitedB.mdf;
  • Type:文件的品类(L:Log File,D:Data File,F:Full Text Catalog);

数据库还原,备份链中断导致差距备份报错案例。翻译如下:

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;

选项:FILE = backup_set_file_number,标志被苏醒的backup set。

当别的备份服务使用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错误日志的应用程序。

 

For example, a backup_set_file_number of 1 indicates the first backup set on the backup medium and a backup_set_file_number of 2 indicates the second backup set. When not specified, the default is 1, except for RESTORE HEADERONLY in which case all backup sets in the media set are processed.

那么那几个新闻是怎么意思啊? 让笔者用一个例证来解释下。 假如你安顿ntbackup.exe去备份D盘。这些磁盘上有一点点SQL Server的数据库相关的数据文件。由于SQL Server要接纳那么些数据文件,由此只要那一个文件在备份时复制将应际而生不均等。为了保险数据库文件在磁盘备份时是一致的,那几个应用程序内部会动用BACKUP DATABASE [databasename] WITH SNAPSHOT命令来备份数据库。当命令实行时,数据库上的I/O会冻结并且备份应用程序被布告继续开展起操作。直到BACKUP WITH SNAPSHOT命令试行到位,数据库的冰冻的I/O当备份命令一旦达成就能够上升。相应的音讯也就能够记录到SQL Server错误日志中。

 

2,验证(Verify)备份文件

 

图片 2

利用Restore VerifyOnly 命令来验证备份文件的有效性,假如备份是卓有成效的,SQL Serer重临验证成功的音信。

末尾检查发掘,刚刚大家在此个时间段有PlateSpin的备份作业在运作(数据库服务器是VMware,系统助理馆员用PlateSpin做D牧马人)。所以也是漏洞相当多日志出现这一个音讯的来由。 其他,关于那一个知识点,也会有下面一些素材供参谋、学习。

 

RESTORE VERIFYONLY
FROM DISK = 'physical_backup_device_name'
[ WITH { MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ,...n ] 
| FILE = backup_set_file_number }] [;]  

 

图片 3

只要评释通过,SQL Server会打字与印刷备份有效的消息:

案例Frozen messages while taking NT Backup for SQL databases

 

The backup set on file 1 is valid.

 

发觉备份日志里面有一条记下将数据库备份到Virtual Device,其实后边在这里篇文章“IO is frozen on database xxx, No user action is required”里面介绍过是因为PlateSpin的备份作业在运作(数据库服务器是VMware,系统管理员用PlateSpin做DR,使用了卷影复制(Volume Shadow Copy VSS)。

分选 Move-To:用于评释磁盘是还是不是有丰盛的Free Space来囤积还原的数据库文件(Data Files 和 Log Files);

有关VDI(VSS)的介绍,能够参照他事他说加以考察上面链接

 

Move子句内定文件的LogicalName,To子句钦点文件的PhysicalName,即,存款和储蓄该文件的帕特h FileName,举个例子:

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

别的,查了眨眼间间,遭逢此消息是还也是有十分大希望是因为在动用别的备份建设方案(NTBackup,BE,Bacula等),它们会对数据库进行类似快速照相的备份(VSS别本

restore verifyonly
from disk = 'D:TestDBBackupFolderSitedb_bak4.bak'
with 
file=1
,move 'Site_TestDB_1' to 'D:TestDBBackupFolderSite_TestDB_1.mdf' 
,move 'Site_TestDB_2' to 'D:TestDBBackupFolderSite_TestDB_2.ndf';
,move 'site_TestDB_log' to 'D:TestDBBackupFloderSite_TestDB_log.ldf'

 

  • 卷影复制 Volume Shadow Copy)。 通过对数据库创立备份快速照相(creating snapshots backups),或者会变成备份链中断,并使原先的差异备份或完整备份无效,那正是怎么在您做差距备份(DIFFERENTIAL BACKUP)的进程中蒙受这么些错误的案由,因为备份链中断了,备份链无效( backup chain invalid)。在间隔备份前须要先做一个完完全全备份。

暗中认可情形下,在还原时,数据备份和日志备份将卷土而来到原来的职责(Original Location),假诺安排将数据库复制到别的Server上,使用Move-To选项是那个必要的,在实施还原操作前,使用Restore VerifyOnly命令,检查是有有充裕的Disk Space,是或不是有神秘的公文命名冲突。

别的关于Database Snapshots (SQL Server)它也可以有部分限量和性质开销的。如下截图所示:

 

If a RESTORE VERIFYONLY statement is used when you plan to relocate a database on the same server or copy it to a different server, the MOVE option might be necessary to verify that sufficient space is available in the target and to identify potential collisions with existing files.

 

其他,蒙受那些荒唐,还应该有十分大可能率有别的三种原因:

二,还原数据文件

图片 4

 

动用restore dabase 命令将积累在备份文件中的 backup 还原成贰个数据库,依照备份的两样,将数据库的苏醒操作分为两有个别:还原数据文件和还原日志文件。

 

1:修改了复苏格局( Recovery Model),因为将数据库的苏醒方式修改为轻松形式也会促成备份链中断。

复苏数据文件的下令,简化

参谋资料:

 

--To Restore an Entire Database from a Full database backup (a Complete Restore):
RESTORE DATABASE database_name  
FROM DISK  'physical_backup_device_name'
[ WITH 
  {[ RECOVERY | NORECOVERY  ]
   | , <general_WITH_options> [ ,...n ]} 
][;]
<general_WITH_options> ::=  
   MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' 
   [ ,...n ] 
 | REPLACE 
 | RESTART 
 | RESTRICTED_USER 
 | FILE = { backup_set_file_number | @backup_set_file_number } 
 | STATS [ = percentage ]

2:特定版本的Bug,这些只看到于SQL Server 二零零七一定版本,具体参谋 平日少之甚少见,只是找出时,发掘有其一情形,所以选定在那。

1,还原选项(RECOVE君越Y | NORECOVE途睿欧Y ),暗中认可值是RECOVE奥迪Q3Y

 

RECOVERY 选项:钦点还原操作将兼具未提交的事务回滚,并使数据库可用;即使后续须要从间隔备份和事务日志备份继续复苏操作,那么必得使用 NORECOVE奥德赛Y选项;RECOVE福特ExplorerY 选项用于恢复生机操作的末段贰个Restore命令中。

 

RECOVERY  option instructs the restore operation to roll back any uncommitted transactions. After the recovery process, the database is ready for use. If subsequent RESTORE operations (RESTORE LOG, or RESTORE DATABASE from differential) are planned, NORECOVERY should be specified instead.

解决方案:

NORECOVERY 选项:钦赐还原操作不回滚未提交的作业,后续需求从出入备份或业务日志备份继续上涨操作,在还原经过的末尾一个Restore命令从前,使用NoRecovery选项。

 

For restoring a database backup and one or more transaction logs or whenever multiple RESTORE statements are necessary (for example, when restoring a full database backup followed by a differential database backup), RESTORE requires the WITH NORECOVERY option on all but the final RESTORE statement. A best practice is to use WITH NORECOVERY on ALL statements in a multi-step restore sequence until the desired recovery point is reached, and then to use a separate RESTORE WITH RECOVERY statement for recovery only.

    能够禁止使用SQL Server VSS Writer服务,来堵住那一个备份技术方案使用卷影复制,测量试验开掘不会影响PlateSpin的功课。

2,移动选项(Move),仅用于恢复生机数据库完整备份

 

MOVE '**logical_file_name_in_backup' TO 'operating_system_file_name' [ ...n ]   **                 

 

在还原经过中,将数据或日志文件移动到新的任务上,暗中认可情况下,数据库的各种文件,都会复苏到原始的地点上(Original Location);要是急需更动数据库文件存款和储蓄的门径,通过move-to 选项,为数据库的每一种文件钦赐新的Location。

 

Specifies that the data or log file whose logical name is specified by logical_file_name_in_backup should be moved by restoring it to the location specified by operating_system_file_name. The logical file name of a data or log file in a backup set matches its logical name in the database when the backup set was created.

Specify a MOVE statement for every logical file you want to restore from the backup set to a new location. By default, the logical_file_name_in_backup file is restored to its original location. 

3,替换选项(Replace),提议不要采取Replace选项,仅用于恢复生机数据库完整备份**

在SQL Server实例中,假如要还原的数量和现有的数据库同名,那么,钦赐Replace选项,SQL Server将会把已存在的同名数据库删除。若无一点名Replace选项,SQL Server会做安检,不会将现有的同名数据库删除。

When the REPLACE option is not specified, a safety check occurs. This prevents overwriting a different database by accident. The safety check ensures that the RESTORE DATABASE statement does not restore the database to the current server if the following conditions both exist:

  • The database named in the RESTORE statement already exists on the current server, and

  • The database name is different from the database name recorded in the backup set.

4,重启选项(Restart)


在数据库还原操作停顿(interrupt)时,重启还原操作,从当中断处重新开头重操旧业操作。

5,限制客户(RESTTiguanICTED_USER),仅用于复苏数据库完整备份**

对新还原的数据库,限制(restrict)客商访谈,只同意剧中人物 db_ownerdbcreator 或 sysadmin 的成员的访谈;

三,还原数据库的日志文件

要施行职业日志的备份,数据库的恢复生机格局(Recovery Mode)必得是FULL,并且数据库必需开展过贰回完整备份;不然,事务日志文件处于活动截断(Auto-Truncate)状态,非常的小概施行专门的职业日志的备份。

图片 5图片 6

--To Restore a Transaction Log:
RESTORE LOG database_name 
 [ <file_or_filegroup_or_pages> [ ,...n ] ]
 [ FROM <backup_device> [ ,...n ] ] 
 [ WITH 
   { 
     [ RECOVERY | NORECOVERY  ]
    | ,  <general_WITH_options> [ ,...n ]
    | , <point_in_time_WITH_options—RESTORE_LOG> 
   } [ ,...n ]
 ] [;]

<point_in_time_WITH_options—RESTORE_LOG>::= 
 | {
   STOPAT = { 'datetime'| @datetime_var } 
 | STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime'] 
 | STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
                 [ AFTER 'datetime'] 
   } 

View Code

在复苏事情日志时,SQL Server援救复苏到时间点,在Restore Log命令中钦命StopAt选项,能够将事情日志还原到实际的时间点。

四,还原数据文件示例

1,依次还原数据库的完好备份,差距备份和事务日志备份

--完整备份还原
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012.bak'
WITH FILE = 1,
STATS=5,
MOVE 'AdventureWorks2012 TO' 'D:SQLServerAdventureWorks2012.mdf',
MOVE 'AdventureWorks2012_Log' TO 'D:SQLServerAdventureWorks2012_log.ldf',
NORECOVERY;
--差异备份还原
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012_2.bak'
WITH FILE = 1,
STATS=5,
NORECOVERY;
--日志备份还原
RESTORE log AdventureWorks2012
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012_3.trn'
WITH FILE = 1,
STATS=5,
RECOVERY;

2,将事情日志还原到某多少个年华点

--日志备份还原到某一个时间点
RESTORE log AdventureWorks2012
FROM DISK = 'Z:SQLServerBackupsAdventureWorks2012_3.trn'
WITH FILE = 1,
STATS=5,
STOPAT='Apr 15, 2016 12:00 AM'
RECOVERY;

五,还原发生的老大等待 PARALLEL_BACKUP_QUEUE

实行 Restore HeaderOnly 命令的对话(Session)长日子处在PARALLEL_BACKUP_QUEUE 等待情况,也无法Kill。

RESTORE HEADERONLY
from disk ='\xxxyyy.bak'

法定文书档案:PARALLEL_BACKUP_QUEUE occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.

PARALLEL_BACKUP_QUEUE 等待表示,在选取并发/多进度苏醒数据库的经过中,八个进度在等候访谈输出结果集。那一个结果集被出现的具备进程共享,因而,在三个历程写入新的多少之前,结果聚集的多寡必得共同。正是说,还原数据库的五个经过必需以系列化的秘籍出口结果集。

出现PARALLEL_BACKUP_QUEUE等待的原因是命令RESTORE HEADERONLY 会将围观备份媒介(backup media)上的享有备份集(backup set),八个备份媒介大概存款和储蓄四个备份集,扫描所有的备份集特别耗费时间。建议选用RESTORE LABELONLY,该命令只会读取备份媒介的尾部(header)消息。

如图,只读取Meida Header,可以高效得到backup meida满含的音讯,然则Header 富含的新闻十分零星。

图片 7

 

仿效文档:

RESTORE (Transact-SQL).aspx)

RESTORE VERIFYONLY (Transact-SQL).aspx)

RESTORE FILELISTONLY (Transact-SQL).aspx)

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:数据库还原,备份链中断导致差距备份报错案例

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。