Monday, April 13, 2009

How to copy SQL Server DBMail configuration to another server

I'm setting up a new SQL Server from scratch and wanted to copy the existing DBMail configuration from the old server. I did some searches and the best I could find were pointers to the msdb.dbo.sysmail_* system tables. I did some trial and error and got everything copied over, so here's how I did it.
  1. Log into the new server
  2. Create a server link from the new server to the old server
  3. Copy the DBMail configuration
I had to log into the new server and do the server link there. From my workstation SQL Server considered it a redirection, and that is a security violation. Save yourself some headaches and just start at the new server. Note that the following SQL script will delete any existing DBMail configuration in the target SQL Server. If you want to keep the existing configuration you'll need to take out the DELETE and SET IDENTITY_INSERT statements and manipulate the account_id and profile_id in the related tables.

SET IDENTITY_INSERT sysmail_account ON
INSERT INTO sysmail_account (account_id, [name], [description], email_address, display_name, replyto_address, last_mod_datetime, last_mod_user)
SELECT * FROM oldserver.msdb.dbo.sysmail_account
SET IDENTITY_INSERT sysmail_account OFF
GO

DELETE sysmail_configuration
GO
INSERT INTO sysmail_configuration (paramname, paramvalue, [description], last_mod_datetime, last_mod_user)
SELECT * FROM oldserver.msdb.dbo.sysmail_configuration
GO

DELETE FROM sysmail_profile
GO
SET IDENTITY_INSERT sysmail_profile ON
INSERT INTO sysmail_profile (profile_id, [name], [description], last_mod_datetime, last_mod_user)
SELECT * FROM oldserver.msdb.dbo.sysmail_profile
SET IDENTITY_INSERT sysmail_profile OFF
GO

DELETE FROM sysmail_principalprofile
GO
INSERT INTO sysmail_principalprofile (profile_id, principal_sid, is_default, last_mod_datetime, last_mod_user)
SELECT * FROM oldserver.msdb.dbo.sysmail_principalprofile
GO

DELETE FROM sysmail_profileaccount
GO
INSERT INTO sysmail_profileaccount (profile_id, account_id, sequence_number, last_mod_datetime, last_mod_user)
SELECT * FROM oldserver.msdb.dbo.sysmail_profileaccount
GO

DELETE FROM sysmail_servertype
GO
INSERT INTO sysmail_servertype (servertype, is_incoming, is_outgoing, last_mod_datetime, last_mod_user)
SELECT * FROM oldserver.msdb.dbo.sysmail_servertype
GO

DELETE FROM sysmail_server
GO
INSERT INTO sysmail_server (account_id, servertype, servername, port, username, credential_id, use_default_credentials,
enable_ssl, flags, last_mod_datetime, last_mod_user)
SELECT * FROM oldserver.msdb.dbo.sysmail_server
GO

No comments:

Post a Comment