Tuesday, April 19, 2011

Locate the most current database backup files

I can't count the times I've needed to go to find my most current .bak file, or even .trn file.  Maybe it was a regularly scheduled restore of the most recent database backup, or I just needed to copy database .bak files from one location to another.  (File System Copy Task)  As you know, you don't always have the timestamp in the database and transaction log backup file names.  This is handy little piece to quickly identify the most recent backup files:

USE msdb;


 DECLARE @Latest TABLE (
   database_name varchar(35),latest_date datetime )
INSERT @Latest (database_name,latest_date)
SELECT database_name,MAX(backup_finish_date) [latest] 
FROM dbo.backupset
GROUP BY database_name
ORDER BY database_name


SELECT 
  b.database_name,
  m.physical_device_name,
  b.backup_finish_date
FROM 
    dbo.backupset b JOIN dbo.backupmediafamily m 
    ON b.media_set_id = m.media_set_id JOIN @Latest l
      ON b.database_name = l.database_name
      AND b.backup_finish_date = l.latest_date
ORDER BY 
b.database_name

NOTE:  See that reference in the JOIN to b.backup_finish_date = l.latest_date.  As is, that will only return the files associated with the most current backupset.backup_finish_date.  That won't be adequate if you have database AND transaction log backups.  In that case, you'd only return the most current .trn file.  I replaced it with this, in order to retrieve the most current .bak AND associated .trn files, for all databases, per instance:

   AND CONVERT(VARCHAR(10),b.backup_finish_date,121) =     
   CONVERT(VARCHAR(10),l.latest_date,121)

No comments:

Post a Comment