Query MSSQL Backup Location - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query MSSQL Backup Location

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2009-11-05 : 07:11:57

Hi is there a way to query the backup location for your db's?

Kind Regards

Pete.

Pradip
Starting Member

32 Posts

Posted - 2009-11-05 : 07:32:35

IF You right click on datbase name and select task restore --> database, it will show you previous backups and paths.

pradipjain

petek
Posting Yak Master

192 Posts

Posted - 2009-11-05 : 08:00:48

yeah i need to do that for 200+ sql servers with multi instances installed on them.....i was hoping to run a query against the db.

i know the MSDB stores some information but can't find what i need.

Kind Regards

Pete.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-05 : 08:05:22

As an approach:
select
database_name,
case type when 'L' then 'Log' else 'Data' end as Backuptype,
physical_device_name,
backup_start_date
from msdb.dbo.backupset a
join msdb..backupmediaset b on a.media_set_id = b.media_set_id
join msdb.dbo.backupmediafamily c on a.media_set_id = c.media_set_id
where type in ('L','D') -- L = Logbackup, D = Databackup
and backup_start_date > getdate()-10
--and 'your_db_name' = database_name
order by backup_start_date desc, database_name asc, Backuptype

No, you're never too old to Yak'n'Roll if you're too young to die.

petek
Posting Yak Master

192 Posts

Posted - 2009-11-05 : 08:08:16

hi thanks for the reply

how do i select the uniqe location for each db?

i cant group the db and select all the different locstions if that makes sense....

Kind Regards

Pete.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-05 : 08:50:28

Gives you the Information for the very last backup:


select
database_name,
Backuptype,
physical_device_name
from
(
select 
row_number() over (partition by database_name,type order by backup_start_date desc) as rownum,
database_name,
case type 
  when 'L' then 'Log' 
  when 'D' then 'Data'
  else '??? '+type 
end as Backuptype,
physical_device_name
from msdb.dbo.backupset a 
join msdb..backupmediaset b on a.media_set_id = b.media_set_id 
join msdb.dbo.backupmediafamily c on a.media_set_id = c.media_set_id 
where backup_start_date > dateadd(hour,-24,getdate()) -- backup within the last 24 hours
)dt
where rownum=1
order by database_name asc, Backuptype