I am currently dealing with a bunch of servers and they have different Database Mail Configurations.
I can see the Database Mail configuration for each of those servers.
I use the following script:
--==========================================================
-- getting the Database Mail Configuration
-- Marcelo Miorelli
-- 1-April-2014
--==========================================================
select @@servername
--SQLSALON1\STOCKALLOCATION
-- http://dba.stackexchange.com/questions/47058/how-can-i-see-the-current-database-mail-configuration
EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
EXEC msdb.dbo.sysmail_help_account_sp
Actually the procedure msdb.dbo.sysmail_help_account_sp
is my favourite in this regard.
Now I would like to email the backup history for the day before (only full and differential backups) and I need to find out the profile name for each of those servers.
I would like to get this done by saving the contents of the above procedure into a temp table or table variable and just query the info from it by the time that I have all the data I need and I am about to email them.
How can I do that for those procedures, specially the sysmail_help_account_sp?
I specifically need to find out the profile name
.
I have used DBA
for the script below.
this is the script I am using to generate the contents of my email:
--=====================================================================================================================
-- sql server backups report in HTML format to be emailed
--http://dba.stackexchange.com/questions/81432/how-do-i-use-powershell-to-get-a-sql-server-backup-status
--marcelo miorelli
--19-nov-2014
--=====================================================================================================================
DECLARE @Body VARCHAR(MAX),
@TableHead VARCHAR(MAX),
@TableTail VARCHAR(MAX)
SET NoCount ON ;
SET @TableTail = '</body></html>' ;
SET @TableHead = '<html><head>' + '<style>'
+ 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt;} '
+ '</style>' + '</head>' + '<body>'
SELECT @Body = ''
SELECT @Body = @Body + '<table cellpadding=0 cellspacing=0 border=0>'
+ '<tr><td bgcolor=#E6E6FA>Database Name </td>'
+ '<td width="100px" bgcolor=#E6E6FA>Backup Size</td>'
+ '<td width="100px" bgcolor=#E6E6FA>Time Taken</td>'
+ '<td width="100px" bgcolor=#E6E6FA>Backup Start Date</td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>First LSN</b></td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>Last LSN</b></td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>Backup Type</b></td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>Server Name</b></td>'
+ '<td width="100px" bgcolor=#E6E6FA><b>Recovery Model</b></td>'
+ '<td width="300px" bgcolor=#E6E6FA>Physical Device Name</td> </tr>'
-- Backup History script of AAsim Adbullah
-- http://blog.sqlauthority.com/2010/11/10/sql-server-get-database-backup-history-for-a-single-database/
SELECT @Body = @Body
+ ( SELECT td = s.database_name, '',
td = CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14))+ ' ' + 'MB', '',
td = CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds', '',
td = s.backup_start_date, '',
td = CAST(s.first_lsn AS VARCHAR(50)), '',
td = CAST(s.last_lsn AS VARCHAR(50)), '',
td = CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END,'',
td = s.server_name, '',
td = s.recovery_model,'',
td = m.physical_device_name,''
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
AND s.backup_start_date >= DATEADD(dd, 0, DATEDIFF(dd, 0,GETDATE() - 1)) -- --- Date check for one day backup history information
AND s.[type] IN ('D','I') -- ignoring the transaction log backups
ORDER BY backup_start_date DESC,
backup_finish_date
FOR
XML PATH('tr')
) + '</table>'
SET @Body = REPLACE(@Body, '_x0020_', SPACE(1))
SET @Body = REPLACE(@Body, '_x003D_', '=')
SET @Body = REPLACE(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
SET @Body = REPLACE(@Body, '<TRRow>0</TRRow>', '')
SELECT @Body = @TableHead + @Body + @TableTail
--select @Body
IF ( @Body IS NULL )
BEGIN
EXEC msdb.dbo.sp_send_dbmail @recipients='mmiorelli@mycompany.co.uk', -- Add Valid Email Id
@subject = 'Backup History Information',
@profile_name = 'DBA', -- Change Profile Name
@body = 'No Backup History Found for past 1 Day',
@body_format = 'HTML' ;
END
ELSE
Begin
EXEC msdb.dbo.sp_send_dbmail @recipients='mmiorelli@amycompany.co.uk', -- Add Valid Email Id
@subject = 'Backup History Information',
@profile_name = 'DBA', -- Change Profile Name
@body = @Body,
@body_format = 'HTML' ;
END
Best Answer
This is actually super easy, and you don't even need to go through the trouble of doing
INSERT... EXEC
with the stored proc.This will return the script info:
You can see that it just queries these system views in msdb:
Which should be easy to integrate into your process.
Hope this helps!