数据库邮件的脚本设置

问题描述

我使用SQL Server 2008 GUI设置数据库邮件配置文件帐户在我的测试服务器上，我现在想要复制到我们的生产数据库。

I've used the SQL Server 2008 GUI to set up database mail profiles & accounts on my test server, and I'd now like to duplicate those to our production database.

有没有办法生成一个脚本来做到这一点？ >

Is there a way to generate a script to do this?

推荐答案

AFAIK，没有办法从SSMS中脚本化，但是您可以在TSQL中创建一个可传输的脚本，并将其重新使用所有的服务器。这是一个很好的例子，让你开始这样做：

AFAIK, there isn't a way to necessarily script this from SSMS but you can create a transportable script in TSQL once and reuse it on all the servers. Here is a good example to get you started with this:

USE [master]
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
-- Create a New Mail Profile for Notifications
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'DBA_Notifications',
       @description = 'Profile for sending Automated DBA Notifications'
GO
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'DBA_Notifications',
    @principal_name = 'public',
    @is_default = 1 ;
GO
-- Create an Account for the Notifications
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQLMonitor',
    @description = 'Account for Automated DBA Notifications',
    @email_address = '[email protected]',  -- Change This
    @display_name = 'SQL Monitor',
    @mailserver_name = 'smtp.domain.com'  -- Change This
GO
-- Add the Account to the Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'DBA_Notifications',
    @account_name = 'SQLMonitor',
    @sequence_number = 1
GO

另一个选项是利用SMO，通过.NET或PowerShell生成脚本。 SMO的参考文献是：

The other option would be to leverage SMO, either through .NET or powershell to generate the scripts. The SMO reference for this would be:

更新：

以下是使用Powershell和SMO来简单的解释一下：

Here is how easy it turned out to be to script this with Powershell and SMO:

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

#Set the server to script from
$ServerName = "ServerName";

#Get a server object which corresponds to the default instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

#Script Database Mail configuration from the server
$srv.Mail.Script();

这篇关于数据库邮件的脚本设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！