Scripting SQL Management Objects in Windows PowerShell

 

This book, by Willis Johnson, shows how to communicate with SQL Server using SQL Management Objects in Windows PowerShell scripts.

Topics include how to create Windows PowerShell scripts that use SQL Management Objects (SMO) to manage and program SQL Server.   

In addition, you will learn about areas of potential difficulty and see how to avoid them—for example, the use of '$' in instance name, quote nesting rules, connection pooling, and schemata.

 

The scripts :

• Load SMO
• Create a server object; get a user’s credentials from the UI into a safestring
• Connect to SQL Server using SQL Server authentication
• Connect to SQL Server using Windows authentication
• Profile a server instance by examining its properties
• Use WMI to examine and configure a server’s Windows service
• Use PowerShell cmdlets to start, stop, and restart a service; check for the existence of a database
• Verify a file path
• Save data in a csv file
• Retrieve data from a csv file
• Create a database, a schema or a table
• Insert data in a table
• Execute ad hoc T-SQL queries
• Retrieve information about views
• Create reports in HTML format
• Display HTML reports in a browser window
• Launch a Windows process
• Tweak data format
  
  A downloadable ZIP archive is included which contains all the example scripts, as well as additional scripts.

The electronic version of the book is available for only US $6.99.

Windows PowerShell and SQL Server 2005 SMO

 

Part 8 (By Muthusamy Anantha Kumar) of this series has published. It demonstrates number of methods on how to use PowerShell in conjunction with SMO to display object properties of all SQL Server Objects.

List databases health with SQL SMO

 

"Get-Databases" function creates a custom "view" of some database properties on the specified SQL server.

 

function Get-SQLDatabases{
    param(
        [string]$server=$(throw "Please specify SQL server name.")
    )

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    $smo = new-object Microsoft.SqlServer.Management.Smo.Server $server
    $count=0;

    $smo.databases | foreach {
        $db = $smo.databases[$_.name];
        $obj = new-object psobject;
        add-member -inp $obj NoteProperty "#" ($count+1);
        add-member -inp $obj NoteProperty "Server" ([string][regex]::replace($db.Parent,"\[|\]","")).toupper();
        add-member -inp $obj NoteProperty "DB Name" $db.Name;
        add-member -inp $obj NoteProperty "ID" $db.id;
        add-member -inp $obj NoteProperty "Size(MB)" $db.size.tostring("N2");
        add-member -inp $obj NoteProperty "Free(MB)" ($db.SpaceAvailable/1024).tostring("N2") ;
        add-member -inp $obj NoteProperty "Status" $db.Status;
        add-member -inp $obj NoteProperty "Last Backup" $db.LastBackupDate;
        add-member -inp $obj NoteProperty "SystemDB" $db.IsSystemObject;
        $obj;
        $count++
    }
}

 

PS > Get-SQLDatabases shayl\sqlexpress | format-table -autosize

# Server                     DB Name ID Size(MB) Free(MB) Status  Last Backup               SystemDB
- ------                       -------      -- --------    --------    ------    -----------                   --------
1 SHAYL\SQLEXPRESS master    1  6.75       1.44        Normal  01/01/0001 00:00:00  True
2 SHAYL\SQLEXPRESS model     3  3.19       1.08        Normal  01/01/0001 00:00:00  True
3 SHAYL\SQLEXPRESS msdb      4  9.44       2.00        Normal  01/01/0001 00:00:00  True
4 SHAYL\SQLEXPRESS tempdb   2  2.69      1.03         Normal  01/01/0001 00:00:00  True
5 SHAYL\SQLEXPRESS test        5  7.13       0.20        Normal  01/01/0001 00:00:00  False

Manage your SQL server with PowerShell and SMO

There is an excellent six part article walk through on Database Journal website by Muthusamy Anantha Kumar (aka The MAK) on PowerShell and SQL Server 2005 SMO. Everything you need to know in order to operate on SQL. The first parts introducing PowerShell concepts and some cmdlets to work with. From part III and on, there are fine examples on connecting to SQL server, collecting server information from different servers, create and backup databases and a lot more. A must read!

Check it out:

PowerShell and SQL Server 2005 SMO – Part I 
PowerShell and SQL Server 2005 SMO – Part II
PowerShell and SQL Server 2005 SMO – Part III
PowerShell and SQL Server 2005 SMO – Part IV
PowerShell and SQL Server 2005 SMO – Part V
PowerShell and SQL Server 2005 SMO – Part VI