This series is a learn by sample blog which I’m hoping any DBA and/or SQL Developer will take advantage to re-use these code snippets. Due to the lack of SQL Server cmdlets, I prefer to use SMO which gives me flexibility to build my own PowerShell commands to automate most of my routine tasks.
1 – Connecting to your SQL Server engine
This take at least three lines of code: (excluding the comment lines)
[sourcecode language=”powershell”]
## – Setting variables with Server and Database name:
$SQLInstanceName = ‘YourSQLServerInstancename’;
## – Loading SMO .NET Assembly: (Required)
[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”) | Out-Null;
## – Connecting to SQL Server (Windows Authentication) and building you table object:
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) SQLInstanceName;
[/sourcecode]
The first line you create a variable holding the SQL Server instance name, then loads the SQL Server assembly “Microsoft.SQLServer.SMO“, and then creating the variable holding the SQL Server objects.
The third line it’s what connect to the server using Windows Authentication. Please be advice that, even you don’t have a Windows Authentication, it will not returned an error and it will return a Null object.
So, if you need to use SQL Server Authentication, you will need to add the following code after creating the “$MySQL” object:
[sourcecode language=”powershell”]
## – Uncomment code block within [#region – #endregion] to enable use of SQL Authentication:
#region – Changing from default Windows to SQL Authentication:
# ## – Variables required to SQL UserID and SQLPassword:
$SQLUserName = “YourSQLUserID”; $sqlPwd = “YourSQLUSerPassword”;
## – Block of Code required to build SQL Authentication:
$MySQL.ConnectionContext.LoginSecure = $false;
$MySQL.ConnectionContext.set_Login($SQLUserName);
$SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;
$MySQL.ConnectionContext.set_SecurePassword($SqlUserPwd);
[/sourcecode]
Now, that you’re connected to the SQL Server instance, you can check the content in the variable $MySQL. To do this the following one-liner help in exploring your object members (Methods and Properties);
[sourcecode language=”powershell”]
$MySQL | Get-member | Out-GridView;
[/sourcecode]
For starters, you might be looking to display some information so you need to look for $MySQL members “Properties” which hold values you can display using the PowerShell “Select-Object” cmdlet. For example, the following oneliner will display the following database properties: Name, Owner, CreateDate, FileGroup, and PrimaryFilePath.
[sourcecode language=”powershell”]
$MySQL.Databases | Select name, Owner, CreateDate, Filegroups, PrimaryFilePath | Format-Table -autosize;
[/sourcecode]
So, Get-Member is you best command to explore your PowerShell objects and understand its content. Another very useful command is the “Out-Gridview” which will use more heavily in the next blog series.
Don’t forget for any of these PowerShell cmdlet you can use the “Get-Help” display the command documentation:
[sourcecode language=”powershell”]
Get-Help Get-Member -ShowWindow
[/sourcecode]
Have fun and Keep learning PowerShell!
