Backup Monitoring Across Multiple Servers

Here is a simple back verification solution. "back verification " I am basically looking to answer following question(s)

1 > When was the last FULL backup was done?
2 > When was the last T.LOG backup was done?.

This solution is very handy if you are monitoring lot of SQL Server(s) in your environment. The solution is split into 3 files as follows,

1 > Batch file
2 > SQL Query file
3 > Server list file

What is in Batch file?

In this batch file I am just calling OSQL with required parameters. The dos "FOR" loop is very fantastic and I found that from the web (thanks to the author).

Cls
for /f "tokens=1,2,3" %%i in (SrvList.txt)  do OSQL.exe -E -S"%%i"   -q -i"BkpDetails.sql" -n -w 2000 >> %random%log.txt

BkpDetails.sql - T-SQL Query

SET NOCOUNT ON
PRINT ' DB Backup details for server ... ['+@@ServerName+'] as on '+ CONVERT(VARCHAR(30), GETDATE())
PRINT ''
SELECT A.NAME [DB_Name], MAX(backup_finish_date) [Bkp Date], MAX(type) [Bkp Type] INTO #Tmp1
FROM master..sysdatabases A
LEFT JOIN msdb..backupset ON a.NAME = database_name
WHERE type = 'L'
GROUP BY A.NAME
ORDER BY [Bkp Date] ASC

SELECT A.NAME [DB_Name], MAX(backup_finish_date) [Bkp Date], MAX(type) [Bkp Type] INTO #Tmp2
FROM master..sysdatabases A
LEFT JOIN msdb..backupset ON a.NAME = database_name
WHERE type = 'D'
GROUP BY A.NAME
ORDER BY [Bkp Date] ASC

select  LEFT(#Tmp2.[DB_Name], 30), LEFT(#Tmp2.[Bkp Date], 30) AS [Last Full Bkp Done At], 
 LEFT(#Tmp1.[Bkp Date], 30) AS [Last T.Log Bkp Done At]
from  #Tmp2
 LEFT JOIN #Tmp1 ON #Tmp2.[DB_Name] = #Tmp1.[DB_Name]
ORDER BY #Tmp1.[Bkp Date] ASC

DROP TABLE #Tmp1
DROP TABLE #Tmp2
PRINT ' ------------------------------ ------------------------------ ------------------------------ '
PRINT ''

What is in SrvList.txt ?

In this txt file I am just listing all my servers that I want to monitor. One server name per line only. In this case I have repeated same server name multiple times to show sample output

LVEPL3KC824\LOCAL
LVEPL3KC824\LOCAL
LVEPL3KC824\LOCAL

Sample Output..

 DB Backup details for server ... [MSSQL_2K5] as on Feb 25 2008  9:37AM

                                Last Full Bkp Done At          Last T.Log Bkp Done At        
 ------------------------------ ------------------------------ ------------------------------
 msdb                           Feb 25 2008  9:33AM            NULL                          
 master                         Feb 25 2008  9:33AM            NULL                          
 model                          Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB                         Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB_Prod                    Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           
 InboxDB                        Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           

 ------------------------------ ------------------------------ ------------------------------

 DB Backup details for server ... [MSSQL_2K5] as on Feb 25 2008  9:37AM

                                Last Full Bkp Done At          Last T.Log Bkp Done At        
 ------------------------------ ------------------------------ ------------------------------
 msdb                           Feb 25 2008  9:33AM            NULL                          
 master                         Feb 25 2008  9:33AM            NULL                          
 model                          Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB                         Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB_Prod                    Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           
 InboxDB                        Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           

 ------------------------------ ------------------------------ ------------------------------

 DB Backup details for server ... [MSSQL_2K5] as on Feb 25 2008  9:37AM

                                Last Full Bkp Done At          Last T.Log Bkp Done At        
 ------------------------------ ------------------------------ ------------------------------
 msdb                           Feb 25 2008  9:33AM            NULL                          
 master                         Feb 25 2008  9:33AM            NULL                          
 model                          Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB                         Feb 25 2008  9:33AM            Feb 25 2008  9:34AM           
 TestDB_Prod                    Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           
 InboxDB                        Feb 25 2008  9:33AM            Feb 25 2008  9:35AM           

 ------------------------------ ------------------------------ ------------------------------