PlateSpin備份時SQL Server的資訊介紹

 

以前寫過一篇文章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"信息，#詳細資訊參考那篇博客，這篇主要是從側面一個佐證、補充！ ## 

# 

其實後面發現，當PlateSpin作業運作時，你會發現資料庫會msdb.dbo.backupset中會記錄一筆資料庫完整備份，備份到Virtual Device的記錄。這個也是側面一個佐證！當然更細、更深入方面的原理、知識點，由於網路上PlateSpine的資料不多，所以自己也沒有時間深入了解。這裡只是我在採集備份資訊時，發現的一個細節而已，當時很是納悶，那個時間點沒有做全備、也沒有備份到虛擬設備（Virtual Device）啊？後面找原因，才發現這個跟PlateSpin有關係。特此記錄一下！

# 

#

SELECT  CONVERT(CHAR(100), SERVERPROPERTY('Servername')) 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 '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   ( CONVERT(DATETIME, bs.backup_start_date, 102) >= CAST('2017-10-18 21:00' 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;

 

#

以上是PlateSpin備份時SQL Server的資訊介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！