Did you ever had to generate a report for all the servers in your organization where you had to present the details of how the backups are happening on the server.
Today, let's create a simple job which will send out the details of the backups taken on the servers and alert you which databases stand in a vulnerable position in a well formatted HTML report.
This job will let you specify, till how many days the backups fails before it alerts you, so that you can specify the threshold. I have only created this to filter out on Full database backups (because that's what I had to implement in my organization ;) and I am too lazy to generalize it for everything). Please go ahead and play around with the script to suit your needs.
So without further discussion, let's jump to the scripts. This job will have a couple of steps to generate header and footer for our HTML document. One step each for all the servers we want to monitor and last step to send the html file over email. Simple enough ...
So the structure of the job would look something like this
Step 1: Create Header
Step 2: MyServer1
Step 3: MyServer2
.
.
.
.
Step n-1: Create Footer
Step n: Send Mail
Each of these job steps except for the last one has a setting to push the output to a HTML file, which will be sent in the last step.
Step 1:
This step is a sqlcmd job step, which will run against the server where we are creating the Backup Report job. It does nothing interesting except pushing our text string to a file. Replace JobServer with the server name where job will be created
sqlcmd -S "JobServer" -Q "Declare @TableHead varchar(max);Set NoCount On;Set @TableHead = '<html><head><style>td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;}</style></head><body><table cellpadding=0 cellspacing=0 border=0><tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td><td align=center><b>Database Name</b></td><td align=center><b>Backup Type</b></td><td align=center><b>Days Since Last Backup</b></td><td align=center><b>Last Backup Date</b></td></tr>';PRINT @TableHead"
And I know there are easier ways to do this. Please, no discussion on that. :)
Step 2:
This step will be created for each server whose details needs to be included in our Backup report.
The first part is the query which runs and extract the data joining sys.sysdatabases and msdb.dbo.backupset tables. This pushes the data into a temporary table #BACKUPDATA. This data is then converted to XML by WITH XML tag in the next select statement.
All the magic happens here. Instead of converting this data to XML, we have specified the tags in such a way that we get formatted HTML from our FOR XML clause.
For each of the server, when you create a new step, replace Server1 with your server name
In the last section of this query, we have used a Replace function. This is just to ensure that the characters returned from FOR XML clause are properly displayed in our HTML report.
TempDB and model are excluded from the list of database using a NOT IN clause. You can add or remove databases as per your requirements.
sqlcmd -E -S Server1 -Q "Declare @Body varchar(max);Set NoCount On;CREATE TABLE #BACKUPDATA (ServerName nvarchar(40), DBName nvarchar(100), BackupType nvarchar(15), [DaysSinceLastBackup] nvarchar(10), [LastBackupDate] nvarchar(10));INSERT INTO #BACKUPDATA SELECT 'Server1' AS [ServerName], B.name as [DBName], case A.type when 'D' THEN 'Full Backup' when 'L' THEN 'Differential' end AS [Type], ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), 'NEVER') as [DaysSinceLastBackup] /*DaysSinceLastBackup*/, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as [LastBackupDate] /*LastBackupDate*/ FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'D' where Database_Name not in ('tempdb', 'model') GROUP BY B.Name , A.type;
SELECT @Body = (SELECT [ServerName] AS [TD], [DBName] AS [TD], BackupType AS [TD], [DaysSinceLastBackup] AS [TD], [LastBackupDate] AS [TD] FROM #BACKUPDATA WHERE [DaysSinceLastBackup]>3 ORDER BY [DBName] FOR XML RAW('TR'), ELEMENTS);Set @Body = Replace(@Body, '_x0020_', space(1));Set @Body = Replace(@Body, '_x003D_', '=');Set @Body = Replace(@Body, '
1', '
');Set @Body = Replace(@Body, '0', '');PRINT @Body;DROP TABLE #BACKUPDATA;"
This step will also require the similar setting as before, only difference is that the Append check box is checked on these steps.
Step 3:
This step is so simple to understand, that it would be an insult to you if I even try to explain. Just replace JobServer with the name of the server where you are creating the job.
sqlcmd -S "JobServer" -Q "PRINT '
'"
Step 4:
The last step is to Bulk insert our HTML file to a variable and send this variable in an email to the designated people.
declare @data as nvarchar(max)
Select @data = Bulkcolumn FROm OPENROWSET
(BULK'J:\Microsoft SQL Server\backup.html', SINGLE_BLOB)X
exec msdb.dbo.sp_send_dbmail @profile_name= 'AuditAlert',
@body = @data,
@body_Format = 'HTML',
@recipients = 'mygroupid@mycompany.com; myid@mycompany.com',
@subject = 'Production Server Backup Report'
Do make sure that you change the location of the HTML file throught the script and in the Job Step properties window, as per the disk location of your Job Server.
You can further enhance this script by not using one job step each for all your servers (as it might not be possible in a bigger environment), but having only one step, which picks up the server names from a table and loops through it. At every loop step, it can push the data into our HTML file. But we might do this on another day.
Till then.... implement this one. More to come soon.
