PowerShell Usage in DBA Work — Case Study 2 / 7


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=, 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;}}
{ 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.

This entry was posted in Administration and tagged . Bookmark the permalink.

One Response to PowerShell Usage in DBA Work — Case Study 2 / 7

  1. Steven Paulich says:

    I have a lot to learn…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s