This article describes how to run a single TSQL script against a list of SQL Servers.

It can be re purpose to run any number of script against a set of SQL servers.

1.  Create a list of servers, place them on a file, and name it server_list.txt
   

The servers should be listed one per line.  You can use any string used to connect to SQL server, and enter port number if needed.

The only prerequisite is that the account running SQL Agent must have sysadmin access to all servers on the list.

2. Create a Batch file, name it run_sql.cmd 
   

The content of the Batch file should look like this:

Batch File:

REM: This batch file runs a SQL scripts against the SQL server 
setlocal 
C:
cd "C:\Program Files\Microsoft SQL Server\bk_report"
REM The echo is to set the headers of the resulting CSV file
echo server_name,SQLVersion,ServicePack,Database,RecoveryMode,FullBackupAge,LogBackupAge,DiffBackupAge, > "C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv"

for /F %%a in (server_list.txt) do (
sqlcmd -S%%a -dmaster -E -m 1 -h-1 -idetail_report.sql -s"," -W -w700 >> "C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv"
)
 
end

3. Place a script with your backup report, name it detail_report.sql.
   

  You can use this script:  http://www.cookingsql.com/2014/10/backup-report/

HINT HINT This is the place where you can put pretty much any script and it will run against all your servers. Not only backup scripts, but also anything that you want to collect information from. Think about it like a poor's man Central Management Server.

4. Configure a SQL Job to run the CMD file.
   

5. Add a second step to email the report.
   

The code for the second step is:

     
    USE msdb
    EXEC sp_send_dbmail
    @profile_name='DBA', -- Please note, this Profile Must exist
    @recipients='DBA_GROUP_Email@us.company.com',
    @subject='Backup Report',
    @body='Please review the attachments. This is an automatic email generated from {server_name}',
    @file_attachments='C:\Program Files\Microsoft SQL Server\bk_report\log\bk_report.csv'

6. Schedule the job to run daily.