[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1357
  • Last Modified:

TSQL to get name of last backup file?

Dear Experts,

Is there any TSQL that would return the full path and name of the most recent database backup file?  I'm looking for the physical location, like "C:\MyBackups\BackupToday.BAK".

Thanks!
BrianMc1958
0
BrianMc1958
Asked:
BrianMc1958
  • 2
  • 2
1 Solution
 
Kevin3NFCommented:
SELECT     TOP 1
   dbo.backupset.database_name,
   dbo.backupmediafamily.physical_device_name,
   dbo.backupset.backup_start_date
FROM        
   dbo.backupmediafamily INNER JOIN
   dbo.backupset ON dbo.backupmediafamily.media_set_id = dbo.backupset.media_set_id
WHERE    
   (dbo.backupset.database_name = N'master')
   AND (dbo.backupset.type = 'D')
ORDER BY
    dbo.backupset.backup_start_date DESC



Add other criteria as necessary

Kevin3NF
0
 
BrianMc1958Author Commented:
I should have said that I'm a newbie.  Sorry.  Plugging that into QA, I'm getting:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.backupmediafamily'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.backupset'.

Should I be replacing "backupmediafamily" with something of my own?
0
 
Kevin3NFCommented:
nope....change the dropdown to the msdb database.  Its probably on master

or use the modified code:

SELECT     TOP 1
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupmediafamily.physical_device_name,
   msdb.dbo.backupset.backup_start_date
FROM        
   msdb.dbo.backupmediafamily INNER JOIN
   msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE    
   (msdb.dbo.backupset.database_name = N'master')
   AND (msdb.dbo.backupset.type = 'D')
ORDER BY
    msdb.dbo.backupset.backup_start_date DESC
0
 
BrianMc1958Author Commented:
That works beautifully.  Thanks a lot.

To anyone reading this, change the 'master' here:

WHERE    
   (msdb.dbo.backupset.database_name = N'master')

to the db you care about...

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now