SQL: Most Recent Full Backup and Restore
Author: jason
Date: 2014-02-05 11:05:51
Category: Musing

This is a combination of some common Backup and Restore scripts. I've added a Temp Table to hold the results and written the queries to return only the most recent Full Backup (D), Full Restore (D), and Log Backup (L). All and all, a pretty nice summary, if I do say so myself...





Code

DECLARE @Server VARCHAR(255)
DECLARE @BackupRestore AS TABLE ([Server] VARCHAR(255)
, [Database] VARCHAR(255), [Date_Of] DATETIME
, [Length] VARCHAR(255) , [Type] VARCHAR(255)
, RecoveryMode VARCHAR(255), Num INT)

SET @Server = @@SERVERNAME;
WITH RestoreInfo AS (
SELECT @Server AS [server],d.name,
restore_date, '' AS [length], 'Restore' AS [type], d.recovery_model_desc,
ROW_NUMBER() OVER (PARTITION BY d.name
ORDER BY restore_date DESC) AS RestoreNum
FROM master.sys.databases d
LEFT JOIN msdb.dbo.restorehistory r on r.destination_database_name = d.name
WHERE COALESCE(restore_type, 'D') IN ('D') )
INSERT INTO @BackupRestore
SELECT * FROM RestoreInfo WHERE RestoreNum = 1;

WITH BackupInfo AS (
SELECT
@Server AS [server], d.name, backup_finish_date
,CAST(DATEDIFF(MI,backup_start_date,backup_finish_date) AS VARCHAR(255)) + ' minutes' AS [length]
,'Backup Full' AS [type], d.recovery_model_desc
, ROW_NUMBER() OVER (PARTITION BY d.name ORDER BY backup_finish_date DESC) AS BackupNum
FROM master.sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE COALESCE([TYPE], 'D') = 'D')
INSERT INTO @BackupRestore
SELECT * FROM BackupInfo WHERE BackupNum = 1;

WITH BackupInfo AS (
SELECT
@Server AS [server], d.name, backup_finish_date
,CAST(DATEDIFF(MI,backup_start_date,backup_finish_date) AS VARCHAR(255)) + ' minutes' AS [length]
,'Backup Log' AS [type], d.recovery_model_desc
, ROW_NUMBER() OVER (PARTITION BY d.name ORDER BY backup_finish_date DESC) AS BackupNum
FROM master.sys.databases d
LEFT JOIN msdb.dbo.backupset b ON b.database_name = d.name
WHERE COALESCE([TYPE], 'L') = 'L')
INSERT INTO @BackupRestore
SELECT * FROM BackupInfo WHERE BackupNum = 1;

SELECT * FROM @BackupRestore ORDER BY Date_Of DESC, [Database] ASC, [Type] ASC




jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009