What are the challenges you face when working across database platforms? Take the survey
Options

Script to find the most recent full backup?

OsolageOsolage Posts: 15
edited September 15, 2008 4:21PM in SQL Backup Previous Versions
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
Thanks,
Ra Osolage

Comments

  • Options
    peteypetey Posts: 2,358 New member
    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
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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
  • Options
    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
  • Options
    Thanks for the answer, Jason. I'm all set now. Much appreciated!
    Thanks,
    Ra Osolage
Sign In or Register to comment.