데이터베이스 백업 기록 조회

데이터베이스에서 백업 기록을 조회하기 위한 쿼리입니다.

1. 데이터베이스 백업 기록 조회

SELECT  
        CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
        bs.database_name,  
        bs.backup_start_date,  
        bs.backup_finish_date, 
        bs.expiration_date, 
        CASE bs.type  
               WHEN 'D' THEN 'Full Database'  
               WHEN 'L' THEN 'Log'  
               WHEN 'I' THEN 'Differential'  
               WHEN 'F' THEN 'File Level'  
               WHEN 'G' THEN 'File Level Differential'  
               WHEN 'P' THEN 'Partial'  
               WHEN 'Q' THEN 'Differential partial'        
        END AS backup_type,  
        convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as 'Backup Size in MB',
        bmf.logical_device_name,  
        bmf.physical_device_name,   
        bs.name AS backupset_name, 
        bs.description 
FROM msdb.dbo.backupmediafamily  bmf
        JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id  
WHERE  (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 10)  
ORDER BY  
        bs.database_name, 
        bs.backup_finish_date