未加星标

Can you run PowerShell in SQL Server Management Studio? YES!

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

Just to be clear!! You can run PowerShell in SQL Server Management Studio.

Not only you can run PowerShell, but you can create scheduled jobs in SQL Agent to run PowerShell scripts. This has been available since SQL Server 2008 (before R2). And, this is a lot better now, as each version are finally providing more “SQL PowerShell cmdlets” to manage your SQL Server in their *”SQLPS” PowerShell module.

*Note: SQLPS Module was introduce with SQL Server 2008.

Nowadays, Thanks to both Aaron Nelson and Christy LeMaire who have contribute to the success of provide new enhancements to SQL Server PowerShell (SQLPS) cmdlets. Check out their tools:

DBA Tools link: https://dbatools.io/

DBA Reports link: https://dbareports.io/

So, YES! You can run PowerShell from SQL Server Management Studio.

How to run PowerShell?

You can Right-Click on most of the SQL Server objects under “ Object Explorer ” and look for “ Start PowerShell “.


Can you run PowerShell in SQL Server Management Studio? YES!

This will open the PowerShell prompt and you are ready to start your adhoc scripting.

Keep in mind, on the latest version of SQL Server (< 2012), the SQL PowerShell module (SQLPS) is loaded and already available. This will create a SQL Server Drive connecting (in this case) to your local instance installation or whichever instance you’re connecting to.


Can you run PowerShell in SQL Server Management Studio? YES!

Notice, in my case, the above image will open a PowerShell prompt and is using PowerShell version 5.1 which is part of my windows 10. The same will be true on earlier OS version of PowerShell. The “Start PowerShell” will open the current PowerShell version installed on that machine.

Another thing to understand, although you already have a set of available cmdlets to manage your SQL Server, you can still expand and build more script with the use SMO ( SQL Server Management Objects ). So, the possibilities to build your own solutions are endless.


Can you run PowerShell in SQL Server Management Studio? YES!

All SMO .NET assemblies are loaded into your system when installing SSMS.

About SQLPS been removed

To be clear! Documentation states that SQLPS “Utility” ( sqlps.exe ) will be removed in the future. But, the SQLPS PowerShell module will still be available. (See reference link)

https://technet.microsoft.com/en-us/library/cc280450(v=sql.130).aspx

This is why you rather use the normal PowerShell console and start using the SQLPS module. Keep in mind, that since PowerShell 3.0, all existing installed modules are automatically loaded and ready to use in your PowerShell session.


Can you run PowerShell in SQL Server Management Studio? YES!
Can you run PowerShell in SQL Server Management Studio? YES!
How do I get SQL PowerShell?

Simple! SQL PowerShell comes included when SQL Server Management Studio(SSMS) is installed. For sometime ago SSMS (SSMS 2012) has been available to install separately (free-of-charge). As a matter of fact, you could install three separate SQL Server Features components without the need of installing SSMS and start scripting against your SQL engine.

The following link shows both latest version of SSMS (16.5.3) and the preview SSMS for SQL Server vNext (RC 17) can be found here:

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms Bonus No need for SSMSGUInor the SQLEngine

Sometimes there’s no need to install a SQL instance, nor SSMS GUI but only the necessary components installed in order to run and scheduled some SQL PowerShell scripts in Windows Server Task Scheduler. I had this scenario on a **server with no SQL engine but needed to run some scheduled SQL PowerShell scripts. Only 3 components are needed:

(Below content extracted from Microsoftlink (Install section) : https://www.microsoft.com/en-us/download/details.aspx?id=52676 )

Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2016

The Microsoft Windows PowerShell Extensions for SQL Server includes a provider and a set of cmdlets that enable administrators and developers to build PowerShell scripts for managing instances of SQL Server. The SQL Server PowerShell Provider delivers a simple mechanism for navigating SQL Server instances that is similar to file system paths. PowerShell scripts can then use the SQL Server Management Objects to administer the instances. The SQL Server cmdlets support operations such as executing Transact-SQL scripts or evaluating SQL Server policies.

Filename: X86 and x64 Package ( PowerShellTools.msi )

Microsoft SQL Server 2016 Shared Management Objects

SQL Server Management Objects (SMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services.

Note: Microsoft SQL Server Management Objects requires Microsoft SQL Server System CLR Types, that is available on this page.

Filename: X86 and x64 Package ( SharedManagementObjects.msi )

Microsoft System CLR Types for Microsoft SQL Server 2016

The SQL Server System CLR Types package contains the components implementing the geometry, geography, and hierarchy id types in SQL Server. This component can be installed separately from the server to allow client applications to use these types outside of the server.

Filename: X86 and x64 Package ( SQLSysClrTypes.msi )

**Note: This can apply to desktop/laptop is you don’t want to install the whole SQL Server CD. As long as, you have remote connection to a SQL Server system, then you just start building scripts. You will save some disk space too.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

分页:12
转载请注明
本文标题:Can you run PowerShell in SQL Server Management Studio? YES!
本站链接:http://www.codesec.net/view/533042.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(91)