Post navigation

Getting Started with PowerShell for SQL Server

The above output shows that a new drive ( SQLSERVER:\. ) is available which is the root drive.To navigate to it and and query the child items use the below command:

Set-Location SQLSERVER:
Get-ChildItem | Select-Object Name

The above output shows the following child items:

SQL

SQLPolicy

SQLRegistration

DataCollection

To navigate to a subfolder, use the cmdlet Set-Location, as shown below:

Set-Location SQL
Get-ChildItem

Each folder in the hierarchy contains different methods which can be invoked and different properties you can access and set values to. To find all the methods and properties available in a folder, use the Get-item and Get-member cmdlets as shown below:

To get all the methods and properties available for an object returned from Invoke-Sqlcmd use the Get-Member cmdlet. The below example uses the parameter –ServerInstance with the cmdlet Get-Item. In the below code (Get-Item .) gets the current server instance name .

Using SQL Server Management Objects with PowerShell

You can leverage the SQL Server SMO class library via PowerShell to connect to SQL Server and retrieve and manipulate data.

Before using SMO objects, you will need to load the SMO class library (ie Microsoft.SqlServer.Smo.dll). The LoadWithPartialName method can load an assembly from the application directory or from the GAC using a partial name. You can then connect to a SQL Server instance using SQL Server SMO using the below code:

Using SMO you can connect to a database and perform any operations that you would normally perform in SSMS. As a simple example the below commands create the database “dummydb” on the SQL Server default instance using SMO: