Showing posts with label backup database status. Show all posts
Showing posts with label backup database status. Show all posts

Friday, January 31, 2014

To get a list of all successful Backups taken till date for a particular Database

DECLARE @DBNAME AS VARCHAR(100) = 'AIRLINE'--DATABASE NAME
SELECT 
  B.MACHINE_NAME,
  B.SERVER_NAME,
 B.DATABASE_NAME AS DBNAME,
  B.BACKUP_START_DATE,
  B.BACKUP_FINISH_DATE,
  CASE 
    WHEN B.[TYPE] = 'D' THEN 'DATABASE'
    WHEN B.[TYPE] = 'I' THEN 'DIFFERENTIAL DATABASE'
    WHEN B.[TYPE] = 'L' THEN 'LOG'
    WHEN B.[TYPE] = 'F' THEN 'FILE OR FILEGROUP'
    WHEN B.[TYPE] = 'G' THEN 'DIFFERENTIAL FILE'
    WHEN B.[TYPE] = 'P' THEN 'PARTIAL'
    WHEN B.[TYPE] = 'Q' THEN 'DIFFERENTIAL PARTIAL'
    ELSE B.[TYPE]
  END BACKUP_TYPE,
  B.EXPIRATION_DATE,
  B.[USER_NAME],
  DATEDIFF(MINUTE,B.BACKUP_START_DATE ,B.BACKUP_FINISH_DATE) AS TOTAL_TIME_IN_MINUTE,
  B.RECOVERY_MODEL,
  B.BACKUP_SIZE/(1024 * 1024 * 1024) AS TOTAL_SIZE_GB,
  BF.PHYSICAL_DEVICE_NAME AS LOCATION
FROM 
  MSDB.DBO.BACKUPSET AS B
INNER JOIN MSDB.DBO.BACKUPMEDIAFAMILY AS BF
 ON B.MEDIA_SET_ID=BF.MEDIA_SET_ID
WHERE
  B.DATABASE_NAME = @DBNAME  
ORDER BY 
  B.BACKUP_START_DATE DESC
GO