SQL Server : query code

Question

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

Answer 1

I think something like the following will work. You already have the database name (sys.databases.name) in your first query, so you can use this to join to MSDB.dbo.BackupSet. Beyond that, you are using an aggregate function in your second query. So you simply need to group by the columns you are selecting from the first query.

SELECT
db.name AS DBName,
mf.type_desc AS Filetype,
mf.Physical_Name AS Location,
(mf.size*8)/1024 AS SizeMB,
DATABASEPROPERTYEX(db.name, 'Status') AS Recovery,
CONVERT( SmallDateTime , MAX(back.Backup_Finish_Date)) as Last_Backup, 
DATEDIFF(d, MAX(back.Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM sys.master_files mf
INNER JOIN sys.databases db
    ON db.database_id = mf.database_id
INNER JOIN MSDB.dbo.BackupSet back
    ON back.Database_Name = db.name
GROUP BY db.name, mf.type_desc, mf.Physical_Name, mf.size

In the future, please post the queries that you have tried so far so that we can help point out where they may have gone wrong.