Using PowerShell to administer Microsoft SQL Server

In one post is impossible to describe all the possibilities that have emerged after the introduction of the support Window Powershell in Microsoft SQL Server, however in this post, I will try to show at least some of these possibilities. In SQL Server, starting with SQL Server 2008, includes two snap-ins:

the
1. Vendor (Provider) SQL Server provides a simple navigation mechanism – "inside" SQL Server, you can use the commands dir, ls, cd, Set-Location, Get-Location, etc.;
the
2. a Set of cmdlets to specify the actions SQL Server (for example, running a sqlcmd script).

Therefore, now you can without the use of third-party software to your scripts in PowerShell all the necessary information to SQL Server.

What it can be useful to the administrator?

So, if you have already installed the client utilities (SQL Server Management Studio), then your system already has the above snap-ins and additional actions are required. If not – you can download Management Studio for free here.
After installing Management Studio, and connect to the desired instance of SQL Server, you can run sqlps directly from there:

If the window that appears sqlps type the command `dir` you can see an interesting picture (note the objects in ObjectExplorer in SSMS, and the result of the command `dir`):

Instead of using sqlps, you can simply add new snap-ins:
the add-pssnapin SqlServerCmdletSnapin100;
add-pssnapin SqlServerProviderSnapin100;
To make sure that they are installed, after reviewing the cmdlet get-pssnapin –registered.
So, after you add these snap-ins you can view the output of Get-Psdrive cmdlet:

Executing cd SQLServer:\SQL\ServerName\InstanceName\, we will achieve the same result as running sqlps from SSMS.
Now, some of the biggest, "Goodies". Cmdlets. We are five cmdlets:

the
1. Invoke-Sqlcmd – almost the same as sqlcmd utility, using this cmdlet, you can execute SQL queries on SQLServer desired.
the
2. Invoke-PolicyEvaluation – checks whether a target set of SQL Server objects to the conditions specified in the schemes of policy-based management.
the
3. Encode-Sqlname – encoded SQL Server identifier. In SQL Server you can set the ID in square brackets in almost any value that can create difficulties when accessing it via PowerShell. To do this, use the Encode-Sqlname, transforming "My:long_and|complexity|identy" to "My%3Along_and%7Ccomplexity%7Cidenty"
the
4. Decode-Sqlname – performs the reverse conversion
the
5. Convert-UrnToPath converts a URN string used by the SMO is in "readable" way.

Most important, for me, is the Invoke-Sqlcmd cmdlet allows you to run arbitrary SQL query on the server.
Imagine a situation that you need to gather information on memory usage in a dozen instances installed on a number of physical servers in your network. To achieve this, you can use a third-party utility that allows you to run a query on a number of instances, you can use the same sqlcmd manually indicating from which servers to obtain the data required. Using PowerShell this task can be solved easier and more elegant. The following script, for example, will bring you information on configuring the ‘Max Server Memory (MB)’, on all instances registered on your computer:

$ServerList = dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | where {$_.Mode-ne "d"}
foreach ($RegisteredSQLs in $ServerList)
{
$dt = invoke-sqlcmd-ServerInstance $RegisteredSQLs.ServerName -database master -Query "SELECT SERVERPROPERTY('servername'), SERVERPROPERTY ('instancename'), value FROM sys.configurations
WHERE name = 'Max Server Memory (MB)'";
echo $dt >> C:\tmp\memory.txt;
}

Please note that in this case, for the account which runs this script needs to be created login in SQL Server e and he should have enough rights to execute the query against the sys.configurations. If you use SQL logins, you need to specify the user name and password in the parameters of the Invoke-Sqlcmd cmdlet.
Now imagine that you have exactly the same set up seven servers, or to add to them one and the same login, or make a backup of all databases on the server... I Hope I was able to show that the use of PowerShell in conjunction with SQL Server can significantly ease the life of database administrator.

Links:

the
1. for General information about SQL Server PowerShell Books Online — msdn.microsoft.com/ru-ru/library/cc281954.aspx
the
2. using the Invoke-Sqlcmd cmdlet, Books Online — msdn.microsoft.com/ru-ru/library/cc281720.aspx
the
3. SQL University — sqlchicken.com/sql-university

Article based on information from habrahabr.ru