Find your backup history in SQL Server

While managing multiple databases, it is often needed tocheck database backup history like when did we taken backup? Where did we takelast few backups? What was the size of last few backup? And many other information.
I have setup maintenance plan for few database, fewdatabases backup taken by third party software and all works on schedule on particulartime even I keep one small T-SQL script handy to check all information I havementioned above.
      bkSet.Backup_Start_Date as BackupStartDate,
      bkSet.Backup_Finish_Date as BackupEndDate,
    MedFam.Physical_Device_NameAS BackupPath,
    MedSet.Software_NameAS SoftwareUsedForBackup,
    bkSet.User_Name ASBackupTakenBy,
    bkSet.Server_NameAS ServerName,
    bkSet.Database_NameAs DatabaseName,
    CASE bkSet.Type     
            WHEN ‘L’ THEN ‘TransactionLogBackup’
            WHEN ‘D’ THEN ‘FullBackup’
            WHEN ‘F’ THEN ‘FileBackup’
            WHEN ‘I’ THEN ‘DifferentialBackup’
        WHEN ‘G’ THEN ‘DifferentialFileBackup’
        WHEN ‘P’ THEN ‘PartialBackup’
        WHEN ‘Q’ THEN ‘DifferentialPartialBackup’
        ELSE NULL END AS BackupType,
    CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB
      msdb..BackupMediaFamily MedFam
      msdb..BackupMediaSet MedSet
      MedFam.Media_Set_ID = MedSet.Media_Set_ID
      msdb..BackupSet bkSet
      bkSet.Media_Set_ID = MedSet.Media_Set_ID
      –keep your database name incondition
      bkSet.Database_Name = ‘Adventureworks’
      –put the date between which you wantto find details of backup
      bkSet.Backup_Finish_Date BETWEEN ‘2011-07-01’ AND ‘2011-07-10’
      bkSet.Backup_Finish_Date DESC

Reference:Ritesh Shah
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
Askme any SQL Server related question at my “ASK Profile

Leave a Reply

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

You are commenting using your 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: