I'm a SQL Sever DBA for a large company. In my position, it makes sense to acquire, and use, applications that automate several of my team's daily tasks. To that end, I have spent laborious months building a DBA repository and reporting solution that combines SQL Server Reporting Services and SQL Server Integration Services to deliver daily reports about the status of the 80+ SQL Servers in my care. The solution pulls together information about SQL Agent jobs, backup successes and failures, user and role permissions, and myriad other types of reporting data that a DBA must analyze and react to, if necessary.

This solution works fine but it does have some limitations:
1. It relies on a successful execution of a growing SSIS package that now takes nearly 15 minutes to complete
2. The package only executes three times a day. Data that is even 15 minutes old can be stale and lead to inaccuracies that could in turn cause bad decisions.

What I needed to complement my existing solution was something we could use to fire off an ad-hoc query against many SQL Servers, simultaneously, and have one result set to review on the fly.

My company uses Red Gate's SQL Backup for their SQL Server backup solution, and I learned recently of a new Red Gate tool, SQL Multi Script, that seemed to me to offer the solution I needed to the problem I've just outlined. I like to put new applications to the test and combine technologies creatively, so I set about using these two tools together to provide me with data that otherwise I couldn't obtain. Getting status information from SQL Backup

To begin with I should state what I know about SQL Backup version 5.0 and beyond. Much of this information was gleaned from Red Gate knowledgebase searches and some, well, let's just call it investigative research.

SQL Backup stores information about the status of backups in a SQL Compact Edition database as well as in log files on the file system of each SQL Server. The database, however, is not readily visible without the use of a stored procedure that Red Gate installs with SQL Backup as part of its server components. The stored procedure in question is:

MASTER..sqbdata
This handy stored procedure takes a SQL statement as a parameter and returns results about backup history, among other things. Figuring this out was not difficult. I simply opened the SQL Backup GUI, ran a report to show failed backups, and captured the TextData via SQL Profiler.

NOTE: You can view the Red Gate SQL Compact Edition database SDF file using SQL Server Management Studio by opening a connection to the Data.SDF file, typically located at: "Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Data\(LOCAL)".

The Red Gate SQL Backup 5.0 GUI does offer a central repository solution to provide much of the same information on failed jobs, via a limited set of reports. However, I realized that this required use of linked servers. I can only speak for myself, but I do not like linked servers, for the same reason that I do not like temp tables or cursors. Not that I do not use them all shamefully, but I try to limit their use whenever I can.

So, enter SQL Multi Script.
Getting backup information from multiple servers

My goal was to define a query that collected all the backup information I required, pass that query as a parameter to the sqbdata stored procedure, and then run it across multiple Servers using SQL Multi Script, getting back a single result set of data for review.

Let's examine the query that I put together that shows all Red Gate backup failures for a date range (the dates can obviously be modified manually, however the goal in this case is to view the previous day's backup history):

DECLARE @sqlstatement VARCHAR(1000)
SELECT @sqlstatement = 'SELECT backup_id,
backup_type,
backuphistory.dbname AS DatabaseName,
backuphistory.backup_start AS StartDate,
backuplog.sqlerrorcode AS SQLErrorCode,
backuplog.code AS BackupErrorCode,
backuplog.message AS Message
FROM backuplog
LEFT JOIN backuphistory ON backuplog.backup_id = backuphistory.id
WHERE backuplog.entry_type = 1
AND backuphistory.backup_start >= ''12/11/07''
AND backuphistory.backup_start >= ''12/12/07''
order by backup_id Desc
' ;

EXECUTE MASTER..sqbdata @sqlstatement
In a perfect world, of course, the DBA team would be notified instantly, via e-mail or page, if any backup failure occurs. SQL Backup has a built-in mail client that will send mail either on success or failure, but what if the e-mail address was setup incorrectly and notifications could not be sent? Yes, this does happen, especially when large configuration changes are occurring, or new servers have come online and, for whatever reason (mainly human error), the server's e-mail option or e-mail address is incorrectly set up.

Full article...