Script to find the most recent full backup?

Comments

• petey Redgate › Posts: 2,358 New member

  September 4, 2008 11:59PM

  Try this:

  SELECT physical_device_name 
  FROM msdb..backupmediafamily
  WHERE media_set_id = (
  	SELECT TOP 1 media_set_id FROM msdb..backupset
  	WHERE type = 'D' AND database_name = !your database name! 
  	ORDER BY backup_finish_date DESC
  	)
  

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• Osolage Posts: 15

  September 8, 2008 4:44PM

  Thanks for the query, Petey. It almost gets me exactly what I want. It actually returns 3 rows, though. The first row contains the backup filepath that I'm looking for. The 2nd and 3rd rows look like the vitrual devices that were used. Below are the 2nd and 3rd records:
  
  SQLBACKUP_7676DD31-8619-4376-BDB3-DF43AC9D9C8601
  SQLBACKUP_7676DD31-8619-4376-BDB3-DF43AC9D9C8602
  
  Is there a column on the table that I can use to filter those records out?
  
  Thanks again,
  Ra

  Thanks,
  Ra Osolage

  0 · Share on Twitter
• petey Redgate › Posts: 2,358 New member

  September 8, 2008 9:41PM

  You can use the family_sequence_number value to pick up only the first backup device. However, if you have backups that are split over multiple files, you need to remove that search argument.

  Peter Yeoh
  SQL Backup Consultant Developer
  Associate, Yohz Software
  Beyond compression - SQL Backup goodies under the hood, updated for version 8

  0 · Share on Twitter
• Jason Cook Posts: 185

  September 9, 2008 5:59AM

  The virtual device names 'SQLBACKUP_...' should only appear if you are performing a multi-threaded backup to a single file. So they can be removed by using a NOT LIKE clause:
  
  

  SELECT physical_device_name
  FROM msdb..backupmediafamily
  WHERE media_set_id = (
     SELECT TOP 1 media_set_id FROM msdb..backupset
     WHERE type = 'D' AND database_name = 'my_database_name'
     ORDER BY backup_finish_date DESC
     )
  AND physical_device_name NOT LIKE 'SQLBACKUP_%';
  

  
  Hope that helps,
  Jason

  0 · Share on Twitter
• Osolage Posts: 15

  September 15, 2008 4:21PM

  Thanks for the answer, Jason. I'm all set now. Much appreciated!

  Thanks,
  Ra Osolage

  0 · Share on Twitter