• 

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