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”
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah
