sponsored by
(http://www.solarwinds.com/)
Managing SQL Server with PowerShell: Part 1 – The Tools By: Michael Otey (https://logicalread.com/author/michael-otey/) By now it’s pretty common knowledge that PowerShell is Microsoft’s preferred management tool and over the past few years, they have really extended the capabilities of PowerShell to where it can be used for just about every management task. There are even some things that can only be done in PowerShell. Even so, PowerShell is mostly a foreign entity to most SQL Server administrators. The vast majority of SQL Server management is done using T-SQL scripts and SQL Server Enterprise Manager. (http://www.solarwinds.com/databaseperformance-analyzer/registration? program=18512&campaign=70150000000RdaM&CCMP= OTC-BAD-LOGICSW_WW_X_NP_X_LD_EN_DPAGEN_SWDPA-20171018_0_X_X-X )
The Experts Database Performance Evangelist
Janis Griffin (https://logicalread.com/author/janisgriffin/) Oracle ACE; SQL Server, Sybase and DB2 expert; Performance Evangelist at SolarWinds
Import the SQLPS Module Microsoft’s recommended way of managing SQL Server with PowerShell is by using the sqlps module. Before using the sqlps module you need to import it into PowerShell using the following Import-Module cmdlet. Import-Module “sqlps” -DisableNameChecking The DisableNameChecking parameter is used to handle the object naming incompatibilities between SQL Server and PowerShell.
Follow SolarWinds
(https://twitter.com/SolarWinds)
(https://www.facebook.com/SolarWinds)
(https://www.linkedin.com/company/166039)
(https://www.youtube.com/user/solarwindsinc)
RSS Feeds All (http://logicalread.com/feed) Databases (http://logicalread.com/database/feed) Systems (http://logicalread.com/category/system/feed) Security (http://logicalread.com/category/security/feed) Cloud (http://logicalread.com/category/cloud)
(http://logicalread.com/wp-content/uploads/2016/04/Powershell-image-1.png) Figure 1 – Importing the sqlps module
ITSM (http://logicalread.com/category/itsm)
Importing sqlps is required when you are working with SQL Server from the standard PowerShell console. As you can see near the top of Figure 1, if you attempt to navigate with the SQL Server PowerShell module before its loaded you will get an error. After importing the SQL Server PowerShell module you can then use the module to navigate and perform other PowerShell commands with the SQL Server instance.
Subscribe to LogicalRead
Running PowerShell from SQL Server Management Studio You can also run PowerShell directly from SQL Server Management Studio (SSMS). If you run PowerShell,you can see launching PowerShell from SSMS in Figure 2.
(http://logicalread.com/wp-content/uploads/2016/04/powershell-image-21.png) Figure 2 – Launching PowerShell from SQL Server Management Studio When you launch the PowerShell console from SSMS the sqlps module is already installed and ready to be used. Unlike the normal blue PowerShell console, by default when you launch the PowerShell console from SSMS its black. Apart from that there’s little difference and it provides the same sqlps functionality.
Navigating Using the SQL Server PowerShell Provider One of the ways that makes PowerShell different from other command shells like the Windows Command Shell is the fact that PowerShell can not only be used to enter commands, it can also be used for navigation into a variety of different sources. For instance, while the Windows Command Shell is capable of navigating the file system, using dir and cd commands PowerShell takes that same model and extends it. PowerShell can navigate the file system and in addition via the use of different providers it can be used to navigate, the Windows registry, Active Directory and even SQL Server. For SQL Server, you can use it to navigate through database objects, such as tables, views, and stored procedures. In the PowerShell console shown in Figure 2, you can see that the SQL Server PowerShell provider is opened by the use of the PS SQLSERVER:\SQL notation at the beginning of the prompt. The first part of the PS SQLSERVER:\SQL path is SQLSERVER:\SQL\
\ where the ComputerName is the name of the system and the InstanceName is the name of the SQL Server instance. In Figure 2, you can see the PowerShell provider uses DEFAULT for the default instance. From this root prompt, you can use the cd and dir commands to navigate through the SQL Server management tree. For instance in Figure 2, you can see how the cd and dir command are used to navigate to the PS SQLSERVER:\SQL\localhost\DEFAULT\Databases\ node which lists all of the databases. You can also continue and drill down into the database and table level. The sqlps provider provides navigation into the following folders: SQLSERVER:\SQL – Database object SQLSERVER:\SQLPolicy – Policy based management objects SQLSERVER:\SQLRegistration – Registered server objects SQLSERVER:\Utility – Utility objects SQLSERVER:\DAC – Data-tier application objects SQLSERVER:\DataCollection – Data collector objects SQLSERVER:\IntegrationServices – Integration Services objects and packages SQLSERVER:\SQLAS – Analysis Services objects
Using the Database Engine cmdlets So far you’ve seen how you can load and use the SQL Server PowerShell provider to navigate a SQL Server instance. You can also use PowerShell to query SQL Server, run commands and more. You can use the SQL Server PowerShell cmdlets (https://msdn.microsoft.com/en-us/library/mt683368.aspx) for these tasks. There are cmdlet’s to create and manage Availability Groups, Backup, Restore and Policies. One of the most important is the Invoke-Sqlcmd (https://msdn.microsoft.com/en-us/library/mt683370.aspx) cmdlet which can be used to execute T-SQL and XQuery commands and scripts. You can see an example of using Invoke-Sqlcmd in the following listing. PS C:\Users\mikeo2> Invoke-Sqlcmd -Query “SELECT @@VERSION;” Column1 ——Microsoft SQL Server 2014 – 12.0.2269.0 (X64) PS SQLSERVER:\>
Pipeline to Part 2 In part 1, I covered the basics of getting started using PowerShell with SQL Server. In part 2,I’ll continue on and dive in to many of the practical examples of performing several common SQL Server management tasks with PowerShell.
Related Posts 3 PowerShell Scripting techniques you must know with SQL Server AlwaysOn (https://logicalread.com/3-powershell-scripting-techniques-you-must-know-with-sql-server-alwaysonpd01/) Managing SQL Server with PowerShell: Part 2 – The Tasks (https://logicalread.com/managing-sqlserver-with-powershell-part-2-the-tasks-mo01/) SQL Server Management Studio: 10 Tips and Tricks (https://logicalread.com/sql-server-managementstudio-10-tips-and-tricks-mo01/) Creating Unattended SQL Server Installations with a Configuration File (https://logicalread.com/creating-unattended-sql-server-installations-with-a-configuration-file-mo01/) Understanding the Basics of Managed Lock Priority in SQL Server 2014 (https://logicalread.com/managed-lock-priority-sql-server-2014-pd01/) First Look at SQL Operations Studio (https://logicalread.com/first-look-sql-operations-studio/)
Leave a Reply Comment
Name * Email * Website Post Comment
Sponsored By
(https://twitter.com/SolarWinds)
Contribute articles Home About SolarWinds (mailto:[email protected]? (https://logicalread.com/) (http://www.solarwinds.com/company/home) subject=I’d%20like%20to%20contribute%20content!) SQL Server Privacy Statement Give feedback (https://logicalread.com/category/databases/sqlserver/) (http://www.solarwinds.com/legal/privacy) (mailto:[email protected]? Oracle Terms of Use subject=I’d%20like%20to%20share%20feedback%20on%20LogicalRead) (https://logicalread.com/category/databases/oracle/) (https://www.solarwinds.com/legal/terms) Contact us DB2 (mailto:[email protected]) (https://logicalread.com/category/databases/db2/)
SAP ASE (https://www.facebook.com/SolarWinds)
(https://www.linkedin.com/company/166039)
(https://logicalread.com/category/databases/sybase/)
SolarWinds uses cookies on its websites to make your online experience easier and better. By using our website, you consent to our use of cookies. For more information on cookies, see our cookie policy. (https://www.solarwinds.com/legal/privacy)
(https://www.youtube.com/user/solarwindsinc)
