How to query file name used for a particular backup?
-
Hi,
I can find out what backups were done on a database by quering backupset table:
select * from msdb.dbo.backupset where database_name = 'MyDB'
But how do I find out what is the file name and the path a particular backup used?
Thanks.
-
SELECT BS.database_name
, BF.physical_name
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupfile BF
ON BF.backup_set_id = BS.backup_set_id
WHERE BS.database_name = 'MyDB'
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
When you do:
select physical_name from msdb.dbo.backupfile
You get a list of the data and log files like these:
e:\mssql\data\MyDB.mdf
d:\logs\MyDB_log.ldf
What I am looking for is the list of backup files like:
f:\backup\mydb\mydb.bak
f:\backup\mydb\mydb.trn
-
Still early on the day
SELECT BS.database_name
, BMF.physical_device_name
, BMF.device_type
FROM msdb.dbo.backupset BS
INNER JOIN msdb.dbo.backupfile BF
ON BF.backup_set_id = BS.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily BMF
on BMF.media_set_id = BS.backup_set_id
WHERE BS.database_name = 'master'
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Unfortunately this query does not work correctly on any of my servers SQL 2000 or 7.0: the database name gets linked to backups for different databases. Is there a chance that "BMF.media_set_id = BS.backup_set_id" join is not quite correct?
-
SELECT b.database_name,
b.backup_start_date,
b.backup_finish_date,
b.user_name,
f.logical_name,
f.physical_name,
mf.physical_device_name,
f.file_type,
f.file_size,
b.backup_size
FROM msdb.dbo.backupfile f,
msdb.dbo.backupset b,
msdb.dbo.backupmediafamily mf
WHERE f.backup_set_id = b.backup_set_id
AND b.media_set_id = mf.media_set_id
AND b.database_name = 'pubs'
ORDER BY b.backup_start_date DESC
MohammedU
Microsoft SQL Server MVP -
making this #é&() mistakes, you can immagine the rest of my day
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Here is the solution:
select
filename from sysfiles
that wil give you something like this :
F
:\MSSQL\MSSQL\data\DEVDAT.mdf
K
:\MSSQL.1\MSSQL\Log\DEVDAT_log.ldf
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply