I am trying to write a T-SQL query for SQL Server to pull some information from all the databases. I will then run this nightly and email the report to who it needs to go to.
I have two separate scripts which I want to merge so all the data is formatted nicely in a table.
SELECT
db.name AS DBName,
type_desc AS Filetype,
Physical_Name AS Location,
(size * 8) / 1024 AS SizeMB,
DATABASEPROPERTYEX(db.name, 'Status') AS Recovery
FROM
sys.master_files mf
INNER JOIN
sys.databases db ON db.database_id = mf.database_id
This returns Name, Type, Location, Size and Status. I would like to merge it with this script:
SELECT
Database_Name,
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM
MSDB.dbo.BackupSet
WHERE
Type = 'd'
GROUP BY
Database_Name
This produces a table with Name, Last backup and days since last backup.
The end result is one query I can run to produce a table with:
Name, Type, Location, Size, Status, Last backup & Days since last backup.
I will then try to work out a way to run this, export it as HTML and email it to myself every morning so I have a quick overview of my environment.
Thanks, Rob