List of all backups from the last full backup

What if one of your database is corrupted and you need to restore all the backups one by one from your last full.

It will be a time consuming and confusing task to get all the backups in order to be restored so as to not break the log chain.

The following script will help you in getting the list of all backups since the last full backup for all the databases in your instance.

It assumes there was atleast one full backup in the last 30 days...if not, you have a bigger problem.

/*

  Author: Siva Ramasamy

 

  Date  : 09/18/2014

  Description: This procedure will return the list of backup files in order from the last fullbackup for the DBs for all the databases.

  Assumption:   This procedure assumes that there was atleast one full backup in the last 30 days. If not :-) you have a bigger problem.

*/

begin

declare @db_name varchar(100)

declare @backup_start_date datetime

declare @most_recent_diffbackup_date datetime

declare @sqlcmd varchar(1000)

--drop table #temp

select bps.database_name     DatabaseName,

    'Full'                BackupType,

    CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,

    bps.backup_start_date BackupStartDate,

    bps.backup_finish_date BackupFinishDate,

    CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,

       bmf.physical_device_name BackupFileName,

       ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date Desc) BackupOrder

into   #temp

from msdb..backupset bps inner join

  msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id

where bps.type = 'D' --  use the Full Backups as the starting point

and   bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.

and bps.backup_start_date > DATEADD (DD,-30,getdate()) -- assuming there was atleast 1 good full backup in the last 30 days

delete from #temp where BackupType = 'Full' and  BackupOrder > 1

--select * from #temp

-- Add and Update the Flag for Verification

Alter table #temp add Processed char(1)

Update #temp set Processed = 'N'

While (select count(1) from #temp where backuptype = 'Full' and  Processed = 'N') > 0

begin

  select top (1) @db_name = databasename, @backup_start_date = BackupStartDate from #temp where processed = 'N'

  --print @db_name

 --print @backup_start_date

 insert into #temp (DatabaseName, BackupType, Size, BackupStartDate, BackupFinishDate, DurationOfBackup, BackupFileName,BackupOrder,Processed)

 select  bps.database_name     DatabaseName,

       'Diff'                BackupType,

      CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,

      bps.backup_start_date BackupStartDate,

      bps.backup_finish_date BackupFinishDate,

      CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,

      bmf.physical_device_name BackupFileName,

   ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date Desc) BackupOrder,

   'P'

    from    msdb..backupset bps inner join

         msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id

    where   bps.type = 'I' --  Pull all the differential backups after the full backup

    and     bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.

    and     bps.backup_start_date > @backup_start_date

 and     bps.database_name  = @db_name

  delete from #temp where BackupType = 'Diff' and  BackupOrder > 1 and DatabaseName = @db_name

    --get all the log backups after most recent full or differential backup

    insert into #temp (DatabaseName, BackupType, Size, BackupStartDate, BackupFinishDate, DurationOfBackup, BackupFileName, BackupOrder,Processed)

 select  bps.database_name     DatabaseName,

       'Log'                BackupType,

   CAST(CAST(bps.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Size,

   bps.backup_start_date BackupStartDate,

   bps.backup_finish_date BackupFinishDate,

      CAST (DATEDIFF(MM,BPS.backup_start_date,bps.backup_finish_date) AS VARCHAR(10)) + ' Mins' DurationOfBackup,

      bmf.physical_device_name BackupFileName,

   ROW_NUMBER() OVER(PARTITION BY bps.database_name ORDER BY bps.Backup_Start_Date) BackupOrder,

   'P'

 from    msdb..backupset bps inner join

   msdb..backupmediafamily bmf on bps.media_set_id = bmf.media_set_id

 where   bps.type = 'L' --  Pull all the Log Backups after the most recent Differential Backup

 and     bps.Is_Copy_Only = '0'   -- no copy only backups since we need to load logs as well.

 and     bps.backup_start_date > (select max(BackupStartDate) from #temp where DatabaseName = @db_name)

 and     bps.database_name  = @db_name

 order by backup_start_date

  Update #temp set Processed = 'P' where databasename = @db_name

end

select * from #temp order by databasename, backupstartdate

end