Script to retrieve SQL Server latest database backup details with backup location

On 19 May, 2015

MS SQL DBA

By : Charith Silva

No Comments

See below a simple script to find latest backup date time and physical location for all the Databases in a given SQL Server instance.

WITH Databse_Backup
AS
(
SELECT @@Servername AS [Server Name] ,
d.Name AS [Databse Name] ,
b.Backup_finish_date AS [Backup date],
bf.Physical_Device_name AS [Physical Device name],
RANK() OVER(PARTITION BY d.Name ORDER by Backup_finish_date desc) as latest
FROM sys.databases d
INNER JOIN msdb..backupset b ON b.database_name = d.name AND b.[type] = 'D'
INNER JOIN msdb.dbo.backupmediafamily bf ON b.media_set_id = bf.media_set_id
)
SELECT [Server Name],[Databse Name] ,[Backup date],[Physical Device name]
FROM Databse_Backup
WHERE latest=1

WITH Databse_Backup

(

SELECT @@Servername AS [Server Name] ,

d.Name AS [Databse Name] ,

b.Backup_finish_date AS [Backup date],

bf.Physical_Device_name AS [Physical Device name],

RANK() OVER(PARTITION BY d.Name ORDER by Backup_finish_date desc) as latest

FROM sys.databases d

INNER JOIN msdb..backupset b ON b.database_name = d.name AND b.[type] = 'D'

INNER JOIN msdb.dbo.backupmediafamily bf ON b.media_set_id = bf.media_set_id

)

SELECT [Server Name],[Databse Name] ,[Backup date],[Physical Device name]

FROM Databse_Backup

WHERE latest=1

Previous Post Next Post

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva