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


/*
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
Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in T-SQL Script and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s