PlateSpin备份时SQL Server的信息介绍-mysql教程-PHP中文网

PlateSpin备份时SQL Server的信息介绍

一个新手

发布： 2017-10-20 11:24:19

原创

1802 人浏览过

以前写过一篇文章IO is frozen on database xxx, No user action is required“，主要是介绍PlateSpin在服务器层面做DR备份时，SQL Server日志里面有"I/O is frozen on database xxxx"以及“I/O was resumed on database xxx"信息，详细信息参考那篇博客，这篇主要是从侧面一个佐证、补充！

clip_image001

其实后面发现，当PlateSpin作业运行时，你会发现数据库会msdb.dbo.backupset中会会记录一条数据库完整备份，备份到Virtual Device的记录。这个也是侧面一个佐证！当然更细、更深入方面的原理、知识点，由于网上PlateSpine的资料不多，自己也没有时间去深入了解。这里只是我在采集备份信息时，发现的一个细节而已，当时很是纳闷，那个时间点没有做全备、也没有备份到虚拟设备（Virtual Device）啊？后面查找原因，才发现这个跟PlateSpin有关系。特此记录一下！

SELECT  CONVERT(CHAR(100), SERVERPROPERTY(&#39;Servername&#39;)) AS servername ,
        bs.database_name ,
        bs.backup_start_date ,
        bs.backup_finish_date ,
        DATEDIFF(MINUTE,bs.backup_start_date, bs.backup_finish_date) AS backup_consume_time,
        bs.expiration_date ,
        CASE bs.type
          WHEN &#39;D&#39; THEN &#39;Full Backup&#39;
          WHEN &#39;I&#39; THEN &#39;Diff Backup&#39;
          WHEN &#39;L&#39; THEN &#39;Log  Bacup&#39;
          WHEN &#39;F&#39; THEN &#39;File Backup&#39;
          WHEN &#39;G&#39; THEN &#39;File Diff&#39;
          WHEN &#39;P&#39; THEN &#39;Partial Backup&#39;
          WHEN &#39;Q&#39; THEN &#39;Partial Diff Backup&#39;
        END AS backup_type ,
        CASE bf.device_type 
          WHEN 2 THEN &#39;Disk&#39;
          WHEN 5 THEN &#39;Tape&#39;
          WHEN 7 THEN &#39;Virtual Device&#39;
          WHEN 105 THEN &#39;permanent backup device&#39;
        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   ( CONVERT(DATETIME, bs.backup_start_date, 102) >= CAST(&#39;2017-10-18 21:00&#39; AS DATETIME))
     AND ( CONVERT(DATETIME, bs.backup_start_date, 102) <= CAST(&#39;2017-10-18 23:59&#39; AS DATETIME))
 AND 
 bs.type=&#39;D&#39; AND bs.database_name=&#39;HistoryData&#39;
ORDER BY bs.database_name ,
         bs.backup_finish_date;

登录后复制

clip_image002