Hello
If you are DBA, sooner or later you will encounter this problem. That is why based on the query found in Dbamemories, we have created a query for Get backup and restore history of all SQL Server instance databases, so with a single execution of this script you will be able to see when it was the last backup, of what type, in what location… For consultation, to verify that the backup was done well the night before, to find the last backup that was made or the one that was done the previous week to restore it to all the databases. For that we have used the undocumented procedure of Microsoft, Sp_MSforeachdb, that executes the command that we indicate to him on all the databases. This would be the query:
EXEC sp_MSforeachdb ' USE?;
select
BS. database_name as TargetDatabase
, BS. Backup_start_date as Operation_Date
, cast (DateDiff (minute, BS. Backup_start_date, BS. Backup_finish_date)/60 as varchar) + ' ' hours ' ' +
Cast (DateDiff (minute, BS. Backup_start_date, BS. backup_finish_date) 60 as varchar) + ' ' minutes ' ' +
Cast (DateDiff (second, BS. Backup_start_date, BS. backup_finish_date) 60 as varchar) + ' ' seconds ' '
as [Duration]
, cast (BS. Backup_size/1024/1024 as decimal (22.2)) as [BackupSize (MB)]
, ' ' BACKUP ' ' as Operation_Type
, Case BS. Type
When ' ' D ' ' Then ' ' Database ' '
When ' ' L ' ' Then ' ' Log ' '
When ' ' I ' ' Then ' ' Differential ' '
end as BackupType
, BS. user_name as [User]
, BMF. Physical_device_name as BackupFile
, BS. server_name as ServerOrigin
, BS. recovery_model
, BS. begins_log_chain
, BS. Is _ Copy_only
, BMS. software_name as BackupSoftware
from msdb. dbo. backupset BS
INNER JOIN msdb. dbo. Backupmediaset BMS
on BS. media_set_id = BMS. media_set_id
inner join msdb. dbo. backupmediafamily BMF
on bms.media_set_id = bmf.media_set_id
where bs.database_name = db_name ()
and bs. server_name = serverproperty (' ' servername ' ')
union all
select
RH. destination_database_name
, rh.restore_date as operation_date
, ' ' Unknown ' ' as [Duration]
, cast (BS. Backup_size/1024/1024 as decimal (22.2)) as [BackupSize (MB)]
, ' ' RESTORE ' ' as Operation_Type
, Case rh.restore_type
when ' ' D ' ' then ' ' Database ' '
when ' ' L ' ' then ' ' Log ' '
when ' ' I ' ' then ' ' Differential ' '
end as BackupType
, rh.user_name as [User]
, bmf.physical_device_name as BackupFile
, bs.server_name as ServerOrigin
, bs.recovery_model
, bs.begins_log_chain
, bs.is _ copy_only
, bms.software_name as BackupSoftware
from msdb. dbo. backupset BS
inner join msdb.dbo. Backupmediaset BMS
on bs.media_set_id = BMS. media_set_id
inner join msdb. dbo. backupmediafamily BMF
on bms.media_set_id = bmf.media_set_id
Inner join msdb.dbo. restorehistory RH
on bs.backup_set_id = rh. backup_set_id
where rh.destination_database_name = Db_name ()
Order by 2 desc
';
We hope that this contribution will help you.
Greetings,
DBA Team.