Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLSailor.com

Anup SivaDas handles the Database Engineering initiatives for Expedia, Inc. (http://www.expedia.com/), having IT experience of more than 9 years. Anup is an active blogger with SQLSailor.com, and can also be found on MSDN SQLServer forums and BeyondRelational.com. He has handled multiple SQLServer projects for various fortune 500 companies, and gained enrich proficiency within Database Administration, Database Architecture for Cloud, Consulting, Virtualization, Build, and Production Support activities. Blog | Twitter | LinkedIn

Get Database information in mail using PowerShell !

Would you love to get a weekly status report of all the databases hosted on your mission critical servers? ,Would you love to get a report which will help you maintain and manage your large shared SQLServer environments neat and clean?

If your answer is Yes, then powershell is your solution. Welcome to the world of powerful powershell(Well, that sounds like a good marketing buzz word !).

logo-powershell

Lets’ think about a large enterprise situation where in you are managing a huge SQLServer shared server which has many databases. Each day your DBA team members will be adding new databases to the server, and you would like to get a status report to ensure that the databases are compliant to the regular database ‘rules’ as you are responsible for the well being of the environment.

Example – No database can have a compatibility level which is less than 110,No databases can have owner other than SA etc.

I’m a great fan of the articles written by Microsoft Scripting Guy, Ed Wilson(B) and one of his articles gave me a quick idea of deploying a powershell function which will pull the database details on a weekly basis.

I slightly modified his code mentioned in the article here (This is a very useful/detailed article, and I highly recommend this to everyone ) and my little function will pull database information such as Name,PageVerify,CompatibilityLevel,Size,LastBackupDate,Recoverymodel,Owner etc and will send across an email.

Note – All credits goes to Ed for his original code. I have great respect for Ed,and if you are interested in powershell, then I highly recommend reading his articles.

Here is the powershell function   -

#Remember to make changes for SMTP area

Function Get-DBInfo ($SQLInstance)

{

    if ($SQLInstance.Contains(“`\”))

        { $location = “SQLSERVER:\SQL\$SQLInstance\Databases” }

    else

        { $location = “SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases” }

    $style = ‘<style>’

    $style = $style + ‘BODY{background-color:peachpuff;}’

    $style = $style + ‘TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}’

    $style = $style + ‘TH{border-width: 1px;padding: 2px;border-style: solid;border-color: black;background-color:thistle;font-size:16}’

    $style = $style+ ‘TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;font-size:14}’

    $style = $style + ‘</style>’

        $body=dir -force  $location | where-object {$_.Name; $_.Refresh()} |

        Select Name, CompatibilityLevel,Pageverify,Size,LastBackupDate,RecoveryModel,Owner |ConvertTo-Html -Head $style -Property Name,CompatibilityLevel,Pageverify,Size,LastBackupDate,RecoveryModel,Owner |Out-String

        #Set your smtpserver information

        Send-MailMessage -To “anupsiva.das@abc.com” -Subject “Database Details from SQL2012 – Notification” –From “dba@abc.com” -SmtpServer “mailserver.abc.com” -Body $Body -BodyAsHtml

}

Get-DBInfo SQL2012

<Modified 01/27/2013> Above code was modified to pass the correct variable <Modified>

If you schedule this function as a SQL Job then you will receive automated emails according to your requirements.

Here is a sample email which was generated by the Get-DBInfo function.

Powershell Notification

Conclusion

Automated reports is going to help DBAs a lot and powershell is making life more easier by helping us to create highly flexible functions.

Thanks for reading and keep watching this space for more.


Comments

Leave a comment on the original post [sqlsailor.com, opens in a new window]

Loading comments...