Bora.Choi

백업 DB에서 최근  백업정보

• DBA는 백업은 중요한 일중에 하나이다. 모든 장비를 확인해 가면서 백업 성공/실패를 확인할 수 없으므로 마지막으로 백업된 날짜를 확인하여 메일로 공지되는것이 좋은 방법이다. 
• 아래 쿼리는 그 내용이며 위 쿼리와 조합을 해서 백업보고를 받게 할 것이다.  
• 해당 보고는 DTS 패키지나 SSIS를 사용해서 처리하거나 JOB으로 장비별로 OSQL을 접속해서 처리하도록 해도 된다. (링크드 서버를 사용하지 않기 위햬) 

1. select max(isnull(datediff(dd,b.backup_start_date,getdate()),0)) as 'Number of Days since last backup',
   b.type as  'Backup type (D-database,L-log)', b.backup_size, d.name as database_name
   from  master..sysdatabases d with (nolock)
    left join msdb..backupset b  with (nolock) on d.name = b.database_name
    and b.backup_start_date = (select max(backup_start_date)
                                      from msdb..backupset b2
                                      where b.database_name = b2.database_name
                                     and b2.type = 'D')
   where d.name != 'tempdb'
   group by d.name, b.type, b.backup_size
   union all
   select max(isnull(datediff(dd,b.backup_start_date,getdate()),0))
   as 'Number of Days since last backup',
   b.type as  'Backup type (D-database,L-log)', b.backup_size, d.name
   as database_name
   from  master..sysdatabases d with (nolock)
   join msdb..backupset b  with (nolock)
   on d.name = b.database_name
   and b.backup_start_date = (select max(backup_start_date)
                                      from msdb..backupset b2
                                      where b.database_name = b2.database_name
                                      and b2.type = 'L')
   group by d.name, b.type, b.backup_size
   order by d.name,b.type   -- optional 
2. ================================================
   다른 방법 
3. ================================================
   SELECT
    cast(database_name AS varchar(30)) AS [DBName],
    cast(user_name AS varchar(30)) AS [UserName],
    str(cast(backup_size AS decimal(20,2)) / 1048576 ,10,2) + ' MB' AS [BackupSize],
    cast(datediff(n,backup_start_date,backup_finish_date) AS varchar(5)) + ' min.' AS [Duration],
    cast(datediff(dd,backup_finish_date,Getdate()) AS varchar(10))  AS [BackupAge],
    convert(varchar(20),backup_finish_date) AS [FinishDate],
    physical_device_name AS [Location],
    mf.device_type
   FROM
    master..sysdatabases DB
    JOIN
    msdb..BACKUPSET BS
    ON DB.name = BS.database_name
    JOIN msdb..backupmediaset MS
    ON
    BS.media_set_id = MS.media_set_id
    JOIN msdb..backupmediafamily MF
    ON
    BS.media_set_id = MF.media_set_id
    JOIN
    (
     SELECT
      max(backup_set_id) AS backup_set_id
     FROM
      msdb..BACKUPSET BS
      JOIN msdb..backupmediaset MS
       ON
       BS.media_set_id = MS.media_set_id
       JOIN msdb..backupmediafamily MF
       ON
       BS.media_set_id = MF.media_set_id
     WHERE
      type = 'D'
      AND mf.device_type <> 7
     GROUP BY database_name
    ) MaxBackup
    ON
    BS.backup_set_id = MaxBackup.backup_set_id
   WHERE
    type = 'D' 
    

이 글은 스프링노트에서 작성되었습니다.