Script to find the most recent full backup?

Osolage

Is there an easy way to find the most recent full backup of a database using T-SQL or the command line tools? I need a script that will do this for me so that I can restore it on a regular basis as a ETL testing database.

Thanks,
Ra osolage

---

Now playing: Push It Up - Less is more
via FoxyTunes

petey

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
	)

Osolage

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

petey

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.

Jason Cook

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

Osolage

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