T-SQL Stored Procedure to Get the UNC Path of Last Database Backup Location

[sourcecode language=”text” wraplines=”false”]

/*
Script to get the last backup file location in the format of a UNC path for specified DB’s

Last modified by Nick Xu on 2013-06-17

Usage:
exec sp__GetLastDatabackupLocation ‘MDP’
*/

use master
go

if exists (select * from sysobjects where name = ‘sp__GetLastDataBackupLocation’ and type = ‘P’)
begin
drop proc sp__GetLastDataBackupLocation
end
go

create procedure sp__GetLastDataBackupLocation
@dbname varchar(50) = N’all’ –display all DB’s last backup time by default
as
begin

if upper(@dbname) = N’ALL’
begin
select tmp.dbname as [DBName],tmp.dumpfile as [LastDataBackupLocation]
from
(
select a.database_name as dbname ,
a.backup_size as backup_size,
case when charindex(‘:’,b.physical_device_name)>0
then
(N’\\’ + @@servername + N’\’ + replace(b.physical_device_name ,’:’,’$’))
else
b.physical_device_name
end as dumpfile,
ROW_NUMBER() over(partition by a.database_name order by a.backup_finish_date desc) as pos
from msdb.dbo.backupset as a
inner join msdb.dbo.backupmediafamily as b
on a.media_set_id=b.media_set_id
where a.database_name not in (N’master’, N’msdb’, N’model’) and a.type = N’D’
) as tmp
where tmp.pos= 1
order by backup_size desc
end
else
begin
select tmp.dbname as [DBName],tmp.dumpfile as [LastDataBackupLocation]
from
(
select a.database_name as dbname ,
a.backup_size as backup_size,
case when charindex(‘:’,b.physical_device_name)>0
then
(N’\\’ + @@servername + N’\’ + replace(b.physical_device_name ,’:’,’$’))
else
b.physical_device_name
end as dumpfile,
ROW_NUMBER() over(partition by a.database_name order by a.backup_finish_date desc) as pos
from msdb.dbo.backupset as a
inner join msdb.dbo.backupmediafamily as b
on a.media_set_id=b.media_set_id
where a.database_name = @dbname and a.type=’D’
) as tmp
where tmp.pos= 1
order by backup_size desc
end

end –end of proc
go
[/sourcecode]