As a DBA in a complex environment, we frequently need to backup various environment information. One of the backups is to script out the configurations / objects, for example, scripting out replication, jobs, policies and policy conditions etc. Almost all of these scripting work can be done via SSMS, however, there is one that cannot be done via SSMS currently.
Case Scenario:
We have 150+ sql server instances (SQL Server 2005+) in several different domains, and each sql server instance may have different database mail (DM) settings, they may use different SMTP servers, or use different authentication ways (such as anonymous/basic authentication) and may also contain different number of profiles/accounts . As required by DR plan, all sql server configurations should be recorded / saved as executable scripts. So how should we approach this?
PowerShell Solution:
## tested on Win7/Win 2K8 R2, PowerShell V3, SQL2K8+ environment
## note: error-handling is removed on purpose for simplicity
add-type -AssemblyName “microsoft.sqlserver.SMO, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″
$s = New-Object -TypeName “Microsoft.SqlServer.Management.Smo.Server” “MyServer”; # put right server
[string]$FilePath=”c:\temp\dbmail.sql”;#put your script name here
$mail = $s.Mail;
if ($mail -ne $null)
{
$mail.script() | out-file $FilePath -Append;
## next is to script out the smtp server / authentication method for the account
## however for the basic authentication, the password is not scripted out as expected
$mail.accounts | % {$_.mailservers | % {$_.script() | out-file $FilePath -Append;}}
}
else
{ write-host ‘No db mail’; }
Advantage Analysis:
Compared with pure T-SQL,
1. PS solution can generate sql file directly in a folder while t-sql solution cannot.
2. PS solution is extremely simple in logic and efficient in coding volume.
Other Thoughts:
Scripting out some objects (esp. different types of objects) out of a big pool of objects should not be handled manually as this is very error-prone. With PS and SMO, it is easy and elegant to script out the required objects. So I think this can be considered as an administration pattern solution, i.e. whenever we need to script out (or delete) multiple objects, use PS + SMO.
There are other times we can use this solution, like when we want to migrate a server to another box, we may need to script out some configuration/settings from current server and apply the scripts to the new server.
