Using PowerShell to Administer Microsoft SQL Server

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

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

Thus, now, you can receive all necessary information from SQL Server in your PowerShell scripts without using third-party software.

How can this be useful for the administrator?

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

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

Instead of using sqlps, you can simply add new snap-ins like this:
add-pssnapin SqlServerCmdletSnapin100;
add-pssnapin SqlServerProviderSnapin100;
You can verify that they are installed by reading the get-pssnapin –registered cmdlet.
So, after adding these snap-ins, you can see the output of the Get-Psdrive cmdlet:

By running cd SQLServer: \ SQL \ ServerName \ InstanceName \, we will achieve the same result as running sqlps from SSMS.
Now a little about the biggest, "goodies." Cmdlets. There are five cmdlets available to us:

1. Invoke-Sqlcmd is practically the same as the utility sqlcmd, with the help of this cmdlet you can execute SQL queries on the required SQLServer.
2. Invoke-PolicyEvaluation - Checks if the target set of SQL Server objects matches the conditions defined in policy-based control schemes.
3. Encode-Sqlname - SQL Server identifier encoding. In SQL Server, you can assign virtually any value to an identifier in square brackets, which can make it difficult to access it using PowerShell. To do this, use the Encode-Sqlname cmdlet that turns "My: long_and | complexity | identy" into "My% 3Along_and% 7Ccomplexity% 7Cidenty"
4. Decode-Sqlname - performs the inverse transform
5. Convert-UrnToPath - converts the URN strings used by the SMO object model to readable paths.

The most important, for me, is the Invoke-Sqlcmd cmdlet that allows you to execute an arbitrary SQL query on the server.
Imagine a situation that you need to collect information on the memory used on a dozen instances installed on a certain number of physical servers in your network. To achieve this, you can use third-party utilities that allow you to perform a query on a certain number of instances, you can use the same sqlcmd, manually specifying from which servers you need to get data. With PowerShell, this task can be solved more simply and elegantly. The following script, for example, will bring you information about setting up '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 under which this script is run, a login should be created on SQL Server and should have enough rights to execute the query to sys.configurations. If you use SQL logins, you must specify the username and password in the parameters of the Invoke-Sqlcmd cmdlet.
')
Now imagine that you need to set up seven servers in exactly the same way, or add the same login to them, or make backups of all databases on the server ... I hope I managed to show that using PowerShell together with SQL Server makes it much easier life of a database administrator.

References:

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