DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 126--------------------------------------------------
        Retrieving full and differential database backups for a test re
       store
       By: srinivasma_exceldbp Date: December 30, 2014, 5:38 am
       ---------------------------------------------------------
       /*Listing 1: Retrieving full and differential database backups
       for a test restore*/
       SELECT  a.server_name ,
       a.database_name ,
       b.physical_device_name ,
       a.[type] ,
       a.backup_finish_date ,
       LogicalFile = d.logical_name ,
       d.File_number
       FROM    msdb.dbo.backupset a
       JOIN msdb.dbo.backupmediafamily b ON a.media_set_id =
       b.media_set_id
       JOIN ( SELECT   backup_finish_date =
       MAX(c1.backup_finish_date) ,
       c1.database_name ,
       c1.server_name
       FROM     msdb.dbo.backupset c1
       JOIN msdb.dbo.backupmediafamily c2
       ON ( c1.media_set_id =
       c2.media_set_id )
       WHERE    c1.type IN ( 'D' )
       GROUP BY c1.database_name ,
       c1.server_name
       ) c ON a.backup_finish_date = c.backup_finish_date
       AND a.database_name = c.database_name
       JOIN msdb.dbo.[backupfile] d ON a.backup_set_id =
       d.backup_set_id
       AND d.file_type = 'D'
       WHERE   a.type IN ( 'D' )
       AND a.database_name NOT IN ( 'master' )
       AND a.is_copy_only = 0
       -- get differential backup entries
       UNION
       SELECT  a.server_name ,
       a.database_name ,
       b.physical_device_name ,
       a.[type] ,
       a.backup_finish_date ,
       LogicalFile = d.logical_name ,
       d.File_number
       FROM    msdb.dbo.backupset a
       JOIN msdb.dbo.backupmediafamily b ON a.media_set_id =
       b.media_set_id
       JOIN ( SELECT   backup_finish_date =
       MAX(c1.backup_finish_date) ,
       c1.database_name ,
       c1.server_name
       FROM     msdb.dbo.backupset c1
       JOIN msdb.dbo.backupmediafamily c2
       ON ( c1.media_set_id =
       c2.media_set_id )
       WHERE    c1.type IN ( 'I' )
       GROUP BY c1.database_name ,
       c1.server_name
       ) c ON a.backup_finish_date = c.backup_finish_date
       AND a.database_name = c.database_name
       JOIN msdb.dbo.[backupfile] d ON a.backup_set_id =
       d.backup_set_id
       AND d.file_type = 'D'
       WHERE   a.type IN ( 'I' )
       AND a.database_name NOT IN ( 'master' )
       AND a.is_copy_only = 0
       ORDER BY database_name ,
       a.backup_finish_date;
       GO
       *****************************************************