Batch File To Fetch Details From Set Of Servers

>> Create one batch file with following code, using this we can collect the server information, which are all in the same domain.

@echo off
@cls
@del CheckReport.txt
@del RepOutput.txt
@cls
@@for /f %%a in (serverlist.txt) do (
@@sqlcmd -S %%a -E -s+ -i test.sql -o RepOutput.txt
@@type RepOutput.txt>>CheckReport.txt
@@echo --------------------------------------------------+----------------------------------------------------------------------------------------------------+-----------+-----------------------+-----------------------+---------------->>CheckReport.txt
)

The above will use 2 text files one is Serverlist.txt and CheckReport.txt

Serverlist.txt is file where we need to mention the server list.

CheckReport is the Output File.

Test.sql is the SQL Query which will run using SQLCMD. The above will use the trusted connection.

I am using Test.SQL to fetch the Backup information and Recovery Model
===========================================================

set nocount on
use msdb

Select Distinct convert(varchar(25),@@Servername) as Servername,
convert(varchar(30),e.database_name) as DBname,
convert(varchar(20),convert(sysname,DatabasePropertyEx

(e.database_name,'Recovery'))),
(Select convert(varchar(25),Max(backup_finish_date) , 100)
From backupset a
Where a.database_name=e.database_name
and a.server_name = @@servername
and type='D'
Group by a.database_name) Database_Bk,

(Select convert(varchar(25),Max(backup_finish_date) , 100)
From backupset b
Where b.database_name=e.database_name
and b.server_name = @@servername
and type='L' Group by b.database_name) Log_Bk,

(Select convert(varchar(25),Max(backup_finish_date) , 100)
From backupset c
Where c.database_name=e.database_name
and c.server_name = @@servername
and type='I' Group by c.database_name) Diff_Bk,

(Select convert(varchar(25),Max(backup_finish_date) , 100)
From backupset d
Where d.database_name=e.database_name
and d.server_name = @@servername
and type='F' Group by d.database_name) File_Bk

From backupset e
Where e.database_name Not in ('tempdb','pubs','northwind','model')
and e.server_name = @@Servername
and e.database_name in (Select Distinct name from master..sysdatabases)

-- never backed up
Union all
select Distinct convert(varchar(25),@@Servername) as Servername,
convert(varchar(30),name) as DBname,
convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),

NULL, NULL , NULL, NULL
from master..sysdatabases as record
where name not in (select distinct database_name from msdb..backupset)
and name not in ('tempdb','pubs','northwind','model')
order by 1,2