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.