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.

    db.name AS DBName,
    type_desc AS Filetype,
    Physical_Name AS Location,
    (size * 8) / 1024 AS SizeMB,
    DATABASEPROPERTYEX(db.name, 'Status') AS Recovery
    sys.master_files mf
    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:

    CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup, 
    DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
    Type = 'd'

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

  • Have you tried to combine them at all? Looked at what columns between the three tables you may be able to join on? – LowlyDBA - John McCall Jun 17 '15 at 14:06
  • I have tried to combine them every which way I could think but keep getting errors. – Rob Jun 17 '15 at 14:45
  • Can you post some of your attempts and the errors you're receiving? – LowlyDBA - John McCall Jun 17 '15 at 14:51
  • I have deleted all my failed attempts but what I think is happening is I am selecting the first lot of data from the table 'sys.master_files mf' and the second lot from 'MSDB.dbo.Backupset'. So I think I need some way to query both tables for the data and them merge or combine the results into a table. Some how.. – Rob Jun 17 '15 at 15:12
  • Yep, see in the first query you're joining two tables together. You need to find a common column to join that third table in the same way. – LowlyDBA - John McCall Jun 17 '15 at 15:34

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.

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.

  • Perfect, Thanks for that. It was something I was working on a few weeks ago and I lost all my attempts so I had to admit defeat and ask for help. Cheers. – Rob Jun 18 '15 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.