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

D = Full

I = Differential database

L = Log

F = File or filegroup

G =Differential file

P = Partial

Q = Differential partial

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

2 Comments

Leave a Reply