Search code examples
sqlsql-serverbackup

Sql to get most recent full backup file for a specific database in sql server

I need an sql command (dbcc or extended stored procedure?) or something that can be called from a sql server stored procedure to get the most recent full backup file available for a specific database. The name of the backup file will be placed in a varchar variable that I can use with a RESTORE DATABASE command in the stored procedure. This procedure will be used to restore from production database to a sandbox/training database, so after the restore completes I need the procedure to continue running so I can make a few modifications to the db.

Solution

  • Just query msdb..backupset (MSDN) on the "source" prod server

    And a working example (SQL Rockstar) too

    Edit, 2018

    SELECT
    bs.database_name,
    bs.backup_start_date,
    bmf.physical_device_name
FROM
    msdb.dbo.backupmediafamily bmf
    JOIN
    msdb.dbo.backupset bs ON bs.media_set_id = bmf.media_set_id
WHERE
    bs.database_name = 'MyDB'
ORDER BY
    bmf.media_set_id DESC;