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.