How to fetch Latest File from Disk based upon the DateTime in the FileName.

• satnam.singh 44358

  Ten Centuries

  Points: 1300

  More actions

  July 11, 2019 at 5:05 pm

  #3662049

  Can anybody please advise on How can we fetch the list of files present in a location based upon the DateTime in it.

  For e.g:-

  If there are following files present on the disk:-

  master_full_backup_20190701.bak

  master_full_backup_20190702.bak

  master_full_backup_20190703.bak

  model_full_backup_20190701.bak

  model_full_backup_20190702.bak

  model_full_backup_20190703.bak

  I want to extract only the master and model backups taken on 1 July 2019 i.e. which has following data appended to it _20190701

   

   

   

• crow1969

  Hall of Fame

  Points: 3001

  More actions

  July 11, 2019 at 6:11 pm

  #3662064

  Better than looking at the file list would be to look at the backup tables in the msdb database.

   

  use msdb;
  
  select bs.Database_name, bs.user_name, bs.backup_start_date, bs.type, bmf.physical_device_name
  
  from backupset bs join
  
  backupmediafamily bmf on bs.media_set_id = bmf.media_set_id
  
  where bs.database_name = 'master'
  
  order by bs.backup_start_date desc

  • This reply was modified 5 years, 8 months ago by  crow1969. Reason: Tried to clean up code tags