• Scripts

    Find the latest databse backup SQL server

    Use the simplest script to find the latest database backup and the location.   -- To get the backup history SELECT top 100 b.server_name ,b.database_name,b.user_name, f.physical_device_name, b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type, b.is_copy_only--,compressed_backup_size/1024/1024 AS C_size_MB FROM MSDB.DBO.BACKUPMEDIAFAMILY F JOIN MSDB.DBO.BACKUPSET B ON (f.media_set_id=b.media_set_id) --WHERE database_name='test' --AND B.type='d' --and b.backup_finish_date >='2018-08-02 22:37:50.000' ORDER BY b.backup_finish_date DESC   -- To get the backup type size based on the date select sum(size_mb )from ( SELECT sum(b.backup_size /1024/1024)as size_mb,b.database_name,b.backup_finish_date FROM MSDB.DBO.BACKUPMEDIAFAMILY F JOIN MSDB.DBO.BACKUPSET B ON (f.media_set_id=b.media_set_id) WHERE b.backup_finish_date between '2012-04-25'and '2012-04-26' AND B.type='D' group by b.database_name,b.backup_finish_date --ORDER BY b.backup_finish_date DESC )as ab   You can add more conditions on where clause http://msdn.microsoft.com/en-us/library/ms186299.aspx. Like backup Type…