Technical Article

Updating Datbase Mail Exchange Server

,

Go to each server that you need to update the exchange server information, old server aand new server and run it when you are satified that the script is giving you what you wan then un - comment the execute line and you are ready to run. Once you run this you email will now be server from the new server.

I have tried this on both Sql server 2005 and Sql server 2008 and It worked great please let me know if you run into any issues please let me know.

I have used Red Gates Multi-Script to execute on each of the server I look after.

 

Mark Huber

=========================================================================
-- Author:        Mark Huber --> mark_the_dba@gmail.com
-- Create date: July 08, 2009
-- Description:    Script to update to a new exchange server 
-- I ran this on Sql2005 and sql2008 servers
-- Usage Agreement: use as you wish share updates here.
--
-- Problem: We are replaceing our exchange server with a new one
-- the issue is that all my dbmail script use this and other
-- develoeprs have set some up for themselves as well
--            not to mention some of these are new server and are already 
-- pointing to this new exchange server.
--            So the issue is large to go out an fix this accross
--     the 55 servers or so that I look after So I created this script
--            top go to each server and run this (I ran this in red-gate multiscript) 
-- 
--
-- **Updates
-- Devloper        Date            Update
-- Mark Huber    jul 7, 2009        Initial Creation
-- =========================================================================
DECLARE @xql VARCHAR(MAX)
DECLARE @crlf VARCHAR(2)
---
DECLARE @oldservername NVARCHAR(100)
DECLARE @newservername NVARCHAR(100)
--
SET @oldservername = 'ex1.old.com'
SET @newservername = 'ex01.new.com'
SET @crlf = CHAR(13)+CHAR(10)
--
DECLARE dbmailgrpcursor CURSOR FOR
Select 'EXECUTE msdb.dbo.sysmail_update_account_sp '+
 @crlf+'@account_name = '+char(39)+sa.name+char(39)+
 @crlf+',@mailserver_name = '+char(39)+@newservername+char(39)+';' AS XQL
FROM [msdb].[dbo].[sysmail_server] AS ss
INNER JOIN msdb.dbo.sysmail_account as sa ON ss.[account_id] = sa.[account_id]
WHERE ss.[servername] = @oldservername
 
---
OPEN dbmailgrpcursor

FETCH NEXT FROM dbmailgrpcursor INTO @xql
     
    WHILE @@FETCH_STATUS = 0
    BEGIN

        PRINT @xql
        
        -- Comment this out when ready to test
        --EXECUTE (@xql) 


        FETCH NEXT FROM dbmailgrpcursor INTO @xql    
    END
--
CLOSE dbmailgrpcursor
DEALLOCATE dbmailgrpcursor
--

--- some other helpful dbmail tables 
--SELECT @@servername ,* From msdb.dbo.sysmail_server 
--SELECT @@servername ,* FROM msdb.dbo.sysmail_profile 
--SELECT @@servername ,* FROM msdb.dbo.sysmail_account

Rate

3 (2)

Share

Share

Rate

3 (2)