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
------------------------------ ------------------------------ ------------------------------
