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
