SQL Script for getting Backup Information

SQL Script to know when all was last backup was being performed ?

SQL Script to know when all was last backup was being performed and what type of backup was taken ?

SQL Script to what type of backup is being configured and when it was last run?

Backuping all databases is the most important maintenance part and a DBA should know, what type of backup and when  was it taken last time.

SQL Script to get last backup date and time and type of backup

/*-------------------------------------------------------------------------------------------------------------------------------Description    :     This stored procedure will send out alert email if there is a blocking which lasted more than specified duration)
-- Copyright 2011 - DBATAG

-- Author        :    DBATAG
-- Created on    :    20/09/2011
-- Version       :    1.0
-- Dependencies  :
--                Table                            Procedure
-- Description      : Get backup date and time of last backup and type of backup and till now, how many backups has been performed on aparticular DB.
----------------------------------------------------------------------------------------------------------------------------*/
SELECT sDB.name AS DatabaseName 
      ,MAX(sDB.recovery_model_desc) AS RecModel 
      ,MAX(BS.backup_start_date) AS LastBackup 
      ,MAX(CASE WHEN BS.type = 'D' 
                THEN BS.backup_start_date END) 
       AS LastFull 
      ,SUM(CASE WHEN BS.type = 'D' 
                THEN 1 END) 
       AS CountFull 
      ,MAX(CASE WHEN BS.type = 'L' 
                THEN BS.backup_start_date END) 
       AS LastLog 
      ,SUM(CASE WHEN BS.type = 'L' 
                THEN 1 END) 
       AS CountLog 
      ,MAX(CASE WHEN BS.type = 'I' 
                THEN BS.backup_start_date END) 
       AS LastDiff 
      ,SUM(CASE WHEN BS.type = 'I' 
                THEN 1 END) 
       AS CountDiff 
      ,MAX(CASE WHEN BS.type = 'F' 
                THEN BS.backup_start_date END) 
       AS LastFile 
      ,SUM(CASE WHEN BS.type = 'F' 
                THEN 1 END) 
       AS CountFile 
      ,MAX(CASE WHEN BS.type = 'G' 
                THEN BS.backup_start_date END) 
       AS LastFileDiff 
      ,SUM(CASE WHEN BS.type = 'G' 
                THEN 1 END) 
       AS CountFileDiff 
      ,MAX(CASE WHEN BS.type = 'P' 
                THEN BS.backup_start_date END) 
       AS LastPart 
      ,SUM(CASE WHEN BS.type = 'P' 
                THEN 1 END) 
       AS CountPart 
      ,MAX(CASE WHEN BS.type = 'Q' 
                THEN BS.backup_start_date END) 
       AS LastPartDiff 
      ,SUM(CASE WHEN BS.type = 'Q' 
                THEN 1 END) 
       AS CountPartDiff 
FROM sys.databases AS sDB 
     LEFT JOIN 
     msdb.dbo.backupset AS BS 
         ON BS.database_name = sDB.name 
WHERE ISNULL(BS.is_damaged, 0) = 0 -- exclude damaged backups          
GROUP BY sDB.name 
ORDER BY sDB.name;

Note : In backupset table, we get the following backups status which means

1. D = Database



2. I = Differential database



3. L = Log



4. F = File or filegroup



5. G =Differential file



6. P = Partial



7. Q = Differential partial



8. NULL - not specified

Backup Type Partial and Differential partial are newly introduced in SQL Server 2005 and still not supported by SSMS and Maintenance Plans. To take this sort of backup we need to manually write SQL  statement like below

BACKUP DATABASE [ANKUR] READ_WRITE_FILEGROUPS TO DISK = N'c:\ANKUR-25Sep2011.BAK'  -- Full Partial  backup 
BACKUP DATABASE [ANKUR] READ_WRITE_FILEGROUPS TO DISK = N'c:\ANKUR-25Sep2011.BAK' WITH  DIFFERENTIAL  -- Differential Partial backup