(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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 *****************************************************