Sql-Server

保存到臨時表中的數據庫郵件配置

  • July 29, 2019

我目前正在處理一堆伺服器，它們有不同的數據庫郵件配置。

我可以看到每個伺服器的數據庫郵件配置。

我使用以下腳本：

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

實際上，msdb.dbo.sysmail_help_account_sp在這方面，該程序是我最喜歡的。

現在我想通過電子郵件發送前一天的備份歷史記錄（僅完整備份和差異備份），我需要找出每個伺服器的配置文件名稱。

我想通過將上述過程的內容保存到臨時表或表變數中來完成此操作，並在我擁有所需的所有數據並且即將通過電子郵件發送給他們時從中查詢資訊。

我怎樣才能為這些程序，特別是 sysmail_help_account_sp 做到這一點？

我特別需要找出profile name. 我已經用於DBA下面的腳本。

這是我用來生成電子郵件內容的腳本：

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

這實際上非常簡單，您甚至無需費心INSERT... EXEC處理儲存過程。

USE msdb

EXEC sp_helptext 'sysmail_help_account_sp'

這將返回腳本資訊：

CREATE PROCEDURE dbo.sysmail_help_account_sp  
  @account_id int = NULL,  
  @account_name sysname = NULL  
AS  
  SET NOCOUNT ON  

  DECLARE @rc int  
  DECLARE @accountid int  
  exec @rc = msdb.dbo.sysmail_verify_account_sp @account_id, @account_name, 1, 0, @accountid OUTPUT  
  IF @rc <> 0  
     RETURN(1)  

  IF (@accountid IS NOT NULL)  
     SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl   
     FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s  
     WHERE a.account_id = s.account_id AND a.account_id = @accountid  

  ELSE  
     SELECT a.account_id, a.name, a.description, a.email_address, a.display_name, a.replyto_address, s.servertype, s.servername, s.port, s.username, s.use_default_credentials, s.enable_ssl  
     FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s  
     WHERE a.account_id = s.account_id  

  RETURN(0)

您可以看到它只是在 msdb 中查詢這些系統視圖：

SELECT *
FROM msdb.dbo.sysmail_account a, msdb.dbo.sysmail_server s  
WHERE a.account_id = s.account_id

這應該很容易集成到您的流程中。

希望這可以幫助！

引用自：https://dba.stackexchange.com/questions/177610

相關問答

Sql-Server

DatabaseMail的綜合故障排除

  • May 23, 2019
檢視問答
Sql-Server

你能備份一個複制的數據庫嗎？

  • February 23, 2022
檢視問答
Sql-Server

達到最大錯誤計數

  • January 27, 2022
檢視問答
Sql-Server

為 SQL Server 2016 數據庫郵件啟用 TLS 1.2

  • January 11, 2022
檢視問答
Sql-Server

大型數據庫分區和僅更新數據的備份（部分備份）

  • October 3, 2021
檢視問答
Sql-Server

如何檢查備份是否可靠？

  • April 22, 2021
檢視問答