Home > Backup & Recovery, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > When did you backup? Where did you backup?

When did you backup? Where did you backup?


If you are managing a lot of databases, you might need to scratch your head for a while to remember where and when did you backed up a database. Fortunately, this information is stored in MSDB database and can be easily retrieved using below tables:

1. backupmediafamily – contains one row for each media family.
2. backupset – contains a row for each backup set.
3. backupmediaset – contains a row for each backup media set.

You can join these tables to find out where/when a backup was made.

SELECT      BS.Backup_Start_Date, BS.Backup_Finish_Date,

            BMF.Physical_Device_Name AS [Backed-Up To],

            BMS.Software_Name AS [Performed Using],

            BS.Name AS [Backup Set Name],

            BS.User_Name AS [Performed By], BS.Server_Name,

            BS.Database_Name,

            CASE BS.Type      WHEN 'D' THEN 'Full'

                              WHEN 'I' THEN 'Differential'

                              WHEN 'L' THEN 'T-Log'

                              WHEN 'F' THEN 'File'

                              WHEN 'G' THEN 'Diff. File'

                              WHEN 'P' THEN 'Partial'

                              WHEN 'Q' THEN 'Diff. Partial'

                              ELSE 'Other' END AS [Backup Type],

            CAST(((BS.Backup_Size/1024)/1204) AS NUMERIC(10,2))

            AS [Size (MB)] ,

            CAST(((BS.Compressed_Backup_Size/1024)/1204) AS NUMERIC(10,2))

            AS [Compressed (MB)],

            CASE BS.Is_Password_Protected WHEN 1 THEN 'Yes'

                                          ELSE 'No' END AS [Password?],

            CASE BMS.Is_Compressed  WHEN 1 THEN 'Yes'

                                    ELSE 'No' END AS [Compressed?]

FROM        msdb..BackupMediaFamily BMF

INNER JOIN  msdb..BackupMediaSet BMS
            ON BMF.Media_Set_ID = BMS.Media_Set_ID

INNER JOIN  msdb..BackupSet BS
           
ON BS.Media_Set_ID = BMS.Media_Set_ID

–WHERE      BS.Database_Name = 'MyDatabaseName'

–AND        BS.Backup_Finish_Date BETWEEN 'StartDate' AND 'EndDate'

ORDER BY    BS.Backup_Finish_Date DESC

You can add a filter for a specific database name and backup duration to filter the result set.

The above query will return all information for all databases present on the current instance of SQL Server.

Below is the output received on my server:

image

Hope This Helps!

Vishal

  1. No comments yet.
  1. No trackbacks yet.