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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: