I need results! Via E-mail, please.

I like the idea of taking several issues, ideas, or problems, and combining what I need to get results.
I was recently poking around Red-Gates site and found a nifty little *FREE* app called SQL Scripts Manager. SQL Scripts Manager is a great application for not only SQL Rooks, but for SQL Vets too. It contains over 25 scripts designed to help simplify the everyday life of a SQL DBA, including automating troubleshooting, diagnostic, and maintenance tasks. It features scripts from people like Dan McClain, Gail Shaw, Glenn Berry [twitter|blog], Louis Davidson [twitter|blog], Ola Hallengren, Phil Factor, Rodney Landrum, Tim Ford [twitter|blog] and Tracy Hamlin [twitter].
Specifically I found a sweet little script by Rodney Landrum, perfectly named “Last good backup”.

Lists the date and time of the last good backup of a particular type, for each database on an instance
This script is useful for checking the progress of backups for all databases on an instance. You can quickly see whether a particular type of backup is missing or delayed, and then investigate further if necessary.
If you need to run an ad hoc backup, you can use the Back up database script included with SQL Scripts Manager.

Here is the exact script:

SELECT sd.name AS [Database],

CASE WHEN bs.type = 'D' THEN 'Full backup'
WHEN bs.type = 'I' THEN 'Differential'
WHEN bs.type = 'L' THEN 'Log'
WHEN bs.type = 'F' THEN 'File/Filegroup'
WHEN bs.type = 'G' THEN 'Differential file'
WHEN bs.type = 'P' THEN 'Partial'
WHEN bs.type = 'Q' THEN 'Differential partial'
WHEN bs.type IS NULL THEN 'No backups'
ELSE 'Unknown (' + bs.type + ')'
END AS [Backup Type],
max(bs.backup_start_date) AS [Last Backup of Type]
FROM master..sysdatabases sd
LEFT OUTER JOIN msdb..backupset bs ON rtrim(bs.database_name) = rtrim(sd.name)
LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name 'tempdb'
GROUP BY sd.name,
bs.type,
bs.database_name
ORDER BY sd.name, [Last Backup of Type]

Wow, that’s a pretty powerful, and useful script, I thought to myself “hmmmm, I’d like to have this script run nightly and email me the results, but how?” This would give me (and my boss) a valuable report every morning by receiving an email, detailing the results of the previous nights backups, in a quick, precise, easy to read method.

Enter Twitter and the wonderful hash tag  #SQLHelp.  I posted a question…

Email the results of a query #sqlhelp

Within the matter of 15 minutes I had a conversation taking place with Marcos Freccia [twitter] starting out on twitter, and ending via Gmail.

Marcos gave me this script:

execute sp_send_dbmail
@profile_name = ‘MCITP Profile’, -- DbMail Profile
@recipients = ‘your@email’, -- Your Email
@subject = ‘Product`s list’,
@body = ‘Some Message’,
@body_format = ‘Text’, -- You can use HTML also, but you have to create some html code before.
@importance = ‘High’, -- Importance Level.
@query = ‘select idProduct, name, quantity from dbo.Products’, -- This is the query, also can be a stored procedure
@execute_query_database = ‘MCITP’, — The database that query going to be executed
@attach_query_result_as_file = 0 , -- If you put 0 the query will be showed in the body, 1 will be a file attached in email
@query_result_header = 1 -- if you put 1 you see the colum name, 0 no.

Perfect, with the combination of the script Marcos had provided and Rodney’s script, I could perform exactly what I set out to do.

Here is the script:

execute sp_send_dbmail
@profile_name = 'your servers mail profile',
@recipients = 'who is going to get the email',
@subject = 'SQL Database Backup Report',
@body = 'important',
@body_format = 'Text',
@importance = 'High',
@query = 'SELECT sd.name AS [Database],
CASE WHEN bs.type = ''D'' THEN ''Full backup''
WHEN bs.type = ''I'' THEN ''Differential''
WHEN bs.type = ''L'' THEN ''Log''
WHEN bs.type = ''F'' THEN ''File/Filegroup''
WHEN bs.type = ''G'' THEN ''Differential file''
WHEN bs.type = ''P'' THEN ''Partial''
WHEN bs.type = ''Q'' THEN ''Differential partial''
WHEN bs.type IS NULL THEN ''No backups''
ELSE ''UNKNOWN (''+ bs.TYPE +'')''
END AS [Backup Type],
max(bs.backup_start_date) AS [Last Backup of Type]
FROM master..sysdatabases sd
LEFT OUTER JOIN msdb..backupset bs ON rtrim(bs.database_name) = rtrim(sd.name)
LEFT OUTER JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name <> ''tempdb''
GROUP BY sd.name,
bs.type,
bs.database_name
ORDER BY sd.name',
@execute_query_database = 'MASTER',
@attach_query_result_as_file = '1',
@query_result_header = '1'

NOTE: The formation change in Rodney’s script where there were single quotes we now have double quotes

If anyone is actually reading this, leave a comment 🙂

A special thanks to Marcos Freccia for his help. And an indirect thanks to Red Gate and Rodney Landrum.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s