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.
SELECT     
      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
FROM       
      msdb..BackupMediaFamily MedFam
INNER JOIN 
      msdb..BackupMediaSet MedSet
ON
      MedFam.Media_Set_ID = MedSet.Media_Set_ID
INNER JOIN 
      msdb..BackupSet bkSet
ON
      bkSet.Media_Set_ID = MedSet.Media_Set_ID
WHERE     
      –keep your database name incondition
      bkSet.Database_Name = ‘Adventureworks’
AND       
      –put the date between which you wantto find details of backup
      bkSet.Backup_Finish_Date BETWEEN ‘2011-07-01’ AND ‘2011-07-10’
ORDER BY   
      bkSet.Backup_Finish_Date DESC

Reference:Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
 http://www.SQLHub.com
Askme any SQL Server related question at my “ASK Profile
Advertisements

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: