Ultimo Backup e Restore
Backup
SELECT sdb.NAME AS DBNAME
,Max(backup_start_date) AS DATA_INICIO
,Max(bs.backup_finish_date) AS DATA_FIM
,CASE
WHEN bs.type = 'D'
THEN 'FULL'
WHEN bs.type = 'I'
THEN 'DIFF'
WHEN bs.type = 'L'
THEN 'LOG'
END AS TIPO
,CONVERT(VARCHAR(8), Convert(TIME, Convert(DATETIME, Datediff(ms, Max(backup_start_date), Max(bs.backup_finish_date)) / 86400000.0))) [DURACAO]
FROM master.sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = sdb.NAME
WHERE (
bs.type = 'D'
OR bs.type = 'I'
OR bs.type = 'L'
OR bs.type IS NULL
)
AND state_desc IN ('ONLINE')
/* AND replica_id IS NULL */
GROUP BY sdb.NAME
,bs.type
ORDER BY sdb.NAME
Restore
SELECT destination_database_name
,bmf.physical_device_name
,restore_date
FROM msdb.dbo.restorehistory
INNER JOIN msdb.dbo.backupset AS bs ON bs.backup_set_id = msdb.dbo.restorehistory.backup_set_id
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id = bmf.media_set_id
WHERE restore_history_id IN (
SELECT MAX(restore_history_id)
FROM msdb.dbo.restorehistory
WHERE restore_type = 'D'
AND destination_database_name IN (
SELECT DISTINCT destination_database_name
FROM msdb.dbo.restorehistory
)
GROUP BY destination_database_name
)
ORDER BY restore_date DESC