Script to find the most recent full backup?

• 
  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
  	)
  

  petey September 04, 2008 23:59

  - 0 +
• 
  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

  Osolage September 08, 2008 16:44

  - 0 +
• 
  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.

  petey September 08, 2008 21:41

  - 0 +
• 
  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

  Jason Cook September 09, 2008 05:59

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

  Osolage September 15, 2008 16:21

  - 0 +

Add comment

Please sign in to leave a comment.