PowerShell import PS module for SQL Server

Friends who have contacted Unix or Linux know that such systems have powerful, omnipotent shell programs called shells. Microsoft's release of PowerShell in the fourth quarter of 2006 marks a significant step in Microsoft's approach to the server sector, providing a simple graphical interface, as well as a robust shell management model similar to UNIX, Linux and other operating systems.

After several years of effort, Microsoft has gradually developed support for PowerShell from server-side products. Without knowing that PowerShell will be eliminated, today we'll take a look at SQL Server support for PowerShell. In essence, SQL Server 2008 should be the first version of SQL Server that supports PowerShell, but its functionality is not perfect, and very few DBAs use this functionality. With more cmdlets added to SQL Server 2012, the purpose of this article is to describe how to manage SQL Server servers through PowerShell in a SQL Server 2012 environment.

I am deploying SQL Server 2012 on the test environment on Windows Server R2 SP1, update the PowerShell version to V3, and see the previous blog post installing wind on Windows 7 and Windows Server R2 oWS PowerShell 3.0, in the PowerShell environment, enter:

Get-psdrive

You can see a screenshot similar to the following:

650) this.width=650; "title=" clip_image002 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image002 "src=" http://s3.51cto.com/wyfs02/M00/59/E5/wKiom1Tus1HT1kg1AADw2o7kMkM623.jpg "border=" 0 "height=" 217 "/ >

Note: There is no psdrive for SQL Server.

Import ps module for SQL Server

Although Microsoft has its own shell for different products, these products all have the same shell environment, and the difference is that they only import different PS modules or PowerShell plugins. This is also true for SQL Server 2012 management, and the way to manage SQL Server from PowerShell is to import the SQL Server Management Module SQLPS module into a Windows PowerShell environment. The module loads and registers the SQL Server snap-in and the administrative assembly.

First, start PowerShell as a system administrator, click the PowerShell icon, right-click on "Run as Administrator" and we need to use the Set-executionpolicy cmdlet to set the appropriate script execution policy. To prevent the execution of malicious scripts, PowerShell has an execution policy that, by default, is set to restricted, that is, restricted, which means that PowerShell scripts cannot be executed. We can use Get-executionpolicy to view the current execution policy, as shown in:

650) this.width=650; "title=" clip_image004 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image004 "src=" http://s3.51cto.com/wyfs02/M01/59/E5/wKiom1Tus1GClGgBAABJXgcripQ609.jpg "border=" 0 "height=" 57 "/ >

PowerShell script is not allowed to execute by default, we can make the corresponding person modification according to the need, the parameters that can be selected are: Restricted, remotesigned, AllSigned, unrestricted. Where restricted is the default setting, scripts cannot run; RemoteSigned means that locally created scripts can run, but scripts downloaded from the Web cannot run unless they have a digital signature signed by a trusted publisher; AllSigned means that a script can run only if it is signed by a trusted publisher. Unrestricted means that script execution is unrestricted, regardless of where it comes from and whether or not they have a signature, which is a completely liberalized strategy. We are here to test the environment, I will directly release, but convenient at the same time, it is necessary to face a certain security risks. As shown in the following:

650) this.width=650; "title=" clip_image006 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image006 "src=" http://s3.51cto.com/wyfs02/M02/59/E5/wKiom1Tus1Lw4TSVAAGREcewAvQ476.jpg "border=" 0 "height=" 141 "/ >

You can then load the Sqlps module, where we use the Import-module cmdlet, which, by default, displays warnings about Encode-sqlname and decode-sqlname, and if you do not want to display such warning information, you can use the The disablenamechecking parameter, as shown in:

650) this.width=650; "title=" clip_image008 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image008 "src=" http://s3.51cto.com/wyfs02/M00/59/E5/wKiom1Tus1PzWXO_AAB8U9ujW_4560.jpg "border=" 0 "height=" 79 "/ >

The SQL Server components are successfully imported into the Windows PowerShell environment at this point.

SQL Server PowerShell Components

The Sqlps module loads two Windows PowerShell snap-ins to implement the appropriate management functions. One of these is called SQL Server PowerShell provider, which allows you to use SQL Server as a drive, just like the file system path we use, where drives are associated with the SQL Server Management object model. The node is based on the object model class, as shown in, using get-psdrive to list drive information, where you can see the multiple out of SQL Server drives.

650) this.width=650; "title=" clip_image010 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image010 "src=" http://s3.51cto.com/wyfs02/M01/59/E2/wKioL1TutF-wVW3SAAFPcF2-aW4015.jpg "border=" 0 "height=" 267 "/ >

Users can locate folders in a command prompt window and perform actions on nodes in the path using commands familiar to them, such as Dir, CD, Del, and Ren. With SQL Server drives, you can access SQL Server objects like the file system, such as instance names, databases, tables, relationships, and so on. We can also view the contents of the SQL Server drive via dir or Get-chilitem. As shown in the following:

650) this.width=650; "title=" clip_image012 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image012 "src=" http://s3.51cto.com/wyfs02/M02/59/E2/wKioL1TutGDDOta3AAOXA-eg4LY157.jpg "border=" 0 "height=" 501 "/ >

Even we can use the dir command to list all the database information on this instance, as shown in:

650) this.width=650; "title=" clip_image014 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image014 "src=" http://s3.51cto.com/wyfs02/M00/59/E2/wKioL1TutGHwLI4fAAG0LuIKz4Q391.jpg "border=" 0 "height=" 269 "/ >

As you can see, there are AdventureWorks, AdventureWorks2012, demo, ReportServer, reportservertempdb Five databases in the current instance, and if unsure can be compared with the results in the graphical interface, As shown in the following:

650) this.width=650; "title=" clip_image016 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image016 "src=" http://s3.51cto.com/wyfs02/M01/59/E2/wKioL1TutGKguhKOAAD60L9K3gA463.jpg "border=" 0 "height=" 318 "/ >

As with other objects, such as auditing, availability groups, jobs, logins, messages, triggers, and so on, the methods are the same, no longer described here.

In addition to the SQL Server PowerShell provider program, you can use cmdlet commands for SQL Server Management, which supports a variety of operations, such as running a Transact-SQL or XQuery The sqlcmd script for the statement. Because of the many commands, you can use the Get-help cmdlet to understand the Help information for each cmdlet at any time during usage. For example, you need to see the Invoke-sqlcmd help information, as shown in:

650) this.width=650; "title=" clip_image018 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image018 "src=" http://s3.51cto.com/wyfs02/M02/59/E2/wKioL1TutGPwe1IuAALtbQdOKQ8800.jpg "border=" 0 "height=" 282 "/ >

Get-help returns a variety of information, such as syntax, parameter definitions, input and output types, and descriptions of the actions performed by the cmdlet. Here, you can also add the-examples parameter to list the appropriate examples:

650) this.width=650; "title=" clip_image020 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image020 "src=" http://s3.51cto.com/wyfs02/M00/59/E2/wKioL1TutGOQMXIOAAJLcm2f0Sg931.jpg "border=" 0 "height=" 397 "/ >

SQL Server PowerShell Management sample

In the previous section we learned that many of the features of SQL Server PowerShell appear as drives after the SQL Server extension is added to PowerShell. SQL Server PowerShell provider makes SQL Server look more like a large disk drive, such as Analysis Services and databases that cause these various components to be like "folders." Configuration settings are presented as "files," and users use specialized PowerShell command sets such as Set-itemproperty and get-itemproperty to manipulate these settings. The Windows PowerShell cmdlet single function command in the form of "verb-noun" is also provided to execute the corresponding Transact-SQL script. Let's take a look at 3 small examples:

Example 1 : Displays the server name of the current SQL Server and the version number of SQL Server. The cmdlet used is Invoke-sqlcmd-query "SELECT @ @version, @ @servername;" Here, I put the result in variable a, as shown in:

650) this.width=650; "title=" clip_image022 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image022 "src=" http://s3.51cto.com/wyfs02/M01/59/E2/wKioL1TutGSwRidKAACUvZ36mSI916.jpg "border=" 0 "height=" 105 "/ >

In general, the output is immediately used, but sometimes the user may need to save the input results so that they can be reused later.

Example 2 : lists the collection subkeys under the Databases node in SQL Server: path. Databases node is stored in the user database information, we are in front of the CD, the dir command is simply viewed, below we change a way. Use set-location sqlserver:\sql\localhost\default\databases to switch to the target path first, and then list the contents by Get-childitem. As shown in the following:

650) this.width=650; "title=" clip_image024 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image024 "src=" http://s3.51cto.com/wyfs02/M02/59/E2/wKioL1TutGWjG6lSAAHCMF9OSx4234.jpg "border=" 0 "height=" 271 "/ >

As you can see in this interface, the database information listed includes the name, status, Recovery mode, and sort characters of the database.

Example 3 : Displays information for tables in the specified database. The database I'm using here is demo, with four tables in this database. As shown, the first is to use:

Set-location sqlserver:\sql\localhost\default\databases\demo\tables switch to the target node, and then use Get-childitem to list the appropriate table information. As shown in the following:

650) this.width=650; "title=" clip_image026 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image026 "src=" http://s3.51cto.com/wyfs02/M02/59/E2/wKioL1TutGawA67vAAH8uT_PRRQ349.jpg "border=" 0 "height=" 281 "/ >

What if you only want to list the tables in the DBO schema? Command instead: Get-childitem | where {$_. Schema-eq "dbo"}, to the V3 version can be modified to: Get-childitem | where Schema–eq "dbo", as shown:

650) this.width=650; "title=" clip_image028 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image028 "src=" http://s3.51cto.com/wyfs02/M00/59/E5/wKiom1Tus1zyAcGiAAPUxiBBowY965.jpg "border=" 0 "height=" 563 "/ >

Example 4 : Use the Cmdlet to make a full backup of the demo database in the current instance and store the backup file under the network path \\192.168.18.235\dbbak with the file name Demo.bak.

First, we need to open the Object Explorer. Toggle the node of the object to be processed, and switch to the database node here. Right-click the object and select Start PowerShell. As shown in the following:

650) this.width=650; "title=" clip_image030 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;margin:0px;border-left:0px;padding-right:0px; "Alt=" Clip_ image030 "src=" http://s3.51cto.com/wyfs02/M01/59/E5/wKiom1Tus1zibn9OAAEVFBah_8A592.jpg "border=" 0 "height=" 318 "/ >

Next, we can use the Backup-sqldatabase command to back up the database, which is a powerful command that shows only how to make a full backup. As shown in the following:

650) this.width=650; "title=" clip_image032 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "alt=" clip_image032 "src=" Http://s3.51cto.com/wyfs02/M02/59/E5/wKiom1Tus13xjh0eAACQgbXwJ-Y403.jpg "border=" 0 "height="/>

Seeing the results shown above shows that the operation was successful, and of course the user can see whether the file exists in the target path, and the entire command is fairly straightforward. Okay, here's the end of our demo.

The SQL Server development team has extensively integrated PowerShell into the product. Administrators need to study PowerShell carefully, which is beneficial for future development. Dear friends, do not belittle PowerShell, not just in SQL Server 2012 products, other Microsoft server is the same, because PowerShell is the Microsoft server Management way of development direction, work together!

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1615406

PowerShell import PS module for SQL Server