Agent Jobs

Find Where Your Backups Are Located

Here’s some SQL Logic to help you find where your backups are located.

find_where_your_sql_backups_are_located

use master;
set nocount on

select
	'database'		= upper(bs.database_name)
,	'time_of_backup'	= replace(replace(replace(left(max(bs.backup_finish_date), 19),':', '-'), 'AM', 'am'), 'PM', 'pm') + ' ' + datename(dw, max(bs.backup_finish_date))
,	'location'		= reverse(right(reverse(upper(bmf.physical_device_name)), len(bmf.physical_device_name) - charindex('\',reverse(bmf.physical_device_name),1) + 1))
,	'backup_file'		= right(bmf.physical_device_name, charindex('\', reverse('\' + bmf.physical_device_name)) - 1)
from
	msdb.dbo.backupset bs join msdb.dbo.backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
--where
--	bs.backup_finish_date > (select getdate()- 30)
--	and bs.database_name  = 'COLLECTIONS'
group by
	bs.database_name, bs.backup_finish_date, bmf.physical_device_name
order by
	bs.database_name, bs.backup_finish_date desc
Advertisements

2 replies »

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