Basic Database Inventory Script

CREATE TABLE #tempresults2
(
database_name NVARCHAR(128),
backup_start_date DATETIME
)
INSERT INTO #tempresults2
SELECT database_name, backup_start_date
FROM msdb.dbo.backupset AS b1
WHERE backup_start_date =
(
SELECT MAX(backup_start_date)
FROM msdb.dbo.backupset AS b2
WHERE b1.database_name = b2.database_name
AND TYPE IN (‘D’, ‘I’)
);
WITH TBL_CTE(name, Size_MB, create_date, compatibility_level, [state], recovery_model, page_verify_option) AS
(
SELECT D.name, CONVERT(DECIMAL(12,2),ROUND(SUM(f.[size])/128.000,2)) AS Size_MB, d.create_date, d.compatibility_level, d.state_desc, d.recovery_model_desc,  d.page_verify_option_desc
FROM SYS.DATABASES D
INNER JOIN SYS.MASTER_FILES F
ON F.database_id = D.database_id
GROUP BY D.name, d.create_date, d.compatibility_level, d.state_desc, d.recovery_model_desc, d.page_verify_option_desc
)
SELECT name, Size_MB, create_date, compatibility_level, [state], recovery_model, page_verify_option,
tr2.backup_start_date AS last_backup, GETDATE() AS inventory_date
FROM TBL_CTE D
LEFT OUTER JOIN #tempresults2 tr2
ON D.name = tr2.database_name;
DROP TABLE #tempresults2

This entry was posted on 4. March 2008 at 19:55 and is filed under SQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response or trackback from your own site.

| Print