Introduction to SQL Server information during PlateSpin backup-Mysql Tutorial-php.cn

Introduction to SQL Server information during PlateSpin backup

一个新手

Release： 2017-10-20 11:24:19

Original

1623 people have browsed it

## I wrote an article before The article IO is frozen on database xxx, No user action is required", mainly introduces PlateSpin in When doing DR backup at the server level, the SQL Server log contains "I/O is frozen on database xxxx" and "I/O was resumed on database xxx" information, For detailed information, please refer to that blog. This article is mainly from the side. Support, supplement!

Introduction to SQL Server information during PlateSpin backup

In fact, I discovered later that when the PlateSpin job is running, you will find that the database will record a complete backup of the database in msdb.dbo.backupset and back it up to the Virtual Device record. This is also a side evidence! Of course, there is not much information on PlateSpine on the Internet about more detailed and in-depth principles and knowledge points, so I don’t have time to understand it in depth. This is just a detail I discovered when collecting backup information. I was very confused at that time. At that time, there was no full backup or backup to the virtual device (Virtual Device)? After searching for the reason later, I found that this was related to PlateSpin. Hereby record it!

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('2017-10-18 23:59' AS DATETIME))
 AND 
 bs.type='D' AND bs.database_name='HistoryData'
ORDER BY bs.database_name ,
         bs.backup_finish_date;

Copy after login

Introduction to SQL Server information during PlateSpin backup ##

The above is the detailed content of Introduction to SQL Server information during PlateSpin backup. For more information, please follow other related articles on the PHP Chinese website!