How can I run this SQL query across all of my databases at once?

Question

I have a query to let me know basic info on backups. I have several SQL servers that I'd like to be able to query at once. Is it possible to run the following query across several servers at one time?

SELECT 
   A.[Server],
   A.database_name, 
   A.last_db_backup_date,
   B.backup_type, 
   B.backup_start_date, 
   B.expiration_date,
   B.backup_size, 
   B.logical_device_name, 
   B.physical_device_name,  
   B.backupset_name,
   B.description
FROM
   (
   SELECT  
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name, 
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily 
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
   GROUP BY
       msdb.dbo.backupset.database_name 
   ) AS A

   LEFT JOIN 

   (
   SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   msdb.dbo.backupset.backup_size, 
   msdb.dbo.backupmediafamily.logical_device_name, 
   msdb.dbo.backupmediafamily.physical_device_name,  
   msdb.dbo.backupset.name AS backupset_name,
   msdb.dbo.backupset.description,
   CASE msdb..backupset.type  
       WHEN 'D' THEN 'Full Database'  
       WHEN 'L' THEN 'Log'  
       WHEN 'I' THEN 'Differential'  
       WHEN 'F' THEN 'File Level'  
       WHEN 'G' THEN 'File Level Differential'  
       WHEN 'P' THEN 'Partial'  
       WHEN 'Q' THEN 'Differential partial'        
   END AS backup_type
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY 
   A.database_name

Answer 1

The easiest way is to create a registered server group in Management Studio, add the server registrations you want, then right-click the group and select "New Query." The query you run will be run against all of the servers (with an additional leading column that indicates which registration it came from):

Of course this relies on all of the servers in the group being alive and reachable.

Some other approaches include Central Management Servers (here are official docs on that) and PowerShell (here's a blog post from Jonathan Kehayias showing how to run a query against all of the servers listed in a text file).

Answer 2

To add to @AaronBertrand's answer you could also use SSIS. I have a package that runs nightly that runs a query very similar to yours and stores the data in a central repository. I loop through a list of instances in a table. I have a 3 part walk through on how to create a package to do the looping here, here and here.