After you install SQL Server 2012, there are two additional downloads you need to gain access to the SQL Server 2012 provider for Windows PowerShell. The first is the Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012, <\/i>and the second is the Microsoft SQL Server 2012 Shared Management Objects. <\/i>Both of these packages come in x86 and in x64 flavors, and they are available via the Microsoft Download site<\/a>.<\/p>\n Note <\/b>To work with the SQL Server provider, you do not have to enable script execution on your system. If you decide to enable script execution on your system, take a look at The Scripting Wife Sets the Script Execution Policy<\/a>.<\/p>\n I am going to create a remote Windows PowerShell console session on my computer running SQL Server. The server’s is named SQL1, and I am going to pass alternate credentials when I make the connection. I type two commands. The first creates a credential object that I store in a variable named $cred<\/b>. When this command runs, a pop-up dialog box appears as shown here.<\/p>\n The second command uses the credential object that I just created to make a remote Windows PowerShell session on the remote computer. When I connect to the remote computer, the Windows PowerShell prompt changes to include the name of the remote computer. Here are the commands I type:<\/p>\n $cred = Get-Credential nwtraders\\administrator<\/p>\n Enter-PSSession -ComputerName sql1 -Credential $cred<\/p>\n Note <\/b>I have SQL Server 2012 running on a machine that is also running Windows Server 2012. As such, Windows PowerShell remoting is already configured—it just works. In Windows Server 2008 or Windows Server 2008 R2, you need to run the Enable-PSRemoting<\/b> command to configure Windows PowerShell remoting. I have written several Hey, Scripting Guy! Blogs<\/a> about this topic.<\/p>\n Now that I have entered a Windows PowerShell session on the remote server running SQL Server 2012, I need to load the SQL Server module for Windows PowerShell. To do this I use the Import-Module<\/b> cmdlet.<\/p>\n Note <\/b>A <\/b>warning message appears because the SQL Server team used two unapproved cmdlet verbs. The unapproved cmdlet verbs are Encode <\/i>and Decode. <\/i>If you do not want to see the warning message (which does not impact performance) use the DisableNameChecking<\/b> <\/i>switch.<\/p>\n The cool thing about the SQL Server provider for Windows PowerShell is that it exposes a SQLSERVER drive. I can use commands like dir<\/b> <\/i>(an alias for the Get-ChildItem<\/b> cmdlet) or cd<\/b> <\/i>(an alias for the Set-Location<\/b> cmdlet) to navigate through SQL Server. Under the covers, the SQL Server provider is masking the complexity of the SQL Server Management Objects<\/a> (SMOs), and every level in the hierarchy exposes a different SMO.<\/p>\n To begin, I type dir<\/b>. <\/i>The results of this command (and my previous commands) are shown here.<\/p>\n As you can see, there are a number of different folders that provide management capabilities at different levels of the SQL Server hierarchy. For more information, see SQL Server PowerShell Provider<\/a> (on Microsoft TechNet, not on MSDN). These folders and the associated capabilities are shown in the image that follows.<\/p>\n To navigate the SQLSERVER drive, use the Get-ChildItem<\/b> and the Set-Location<\/b> cmdlets (or use the dir<\/b> <\/i>and cd<\/b> <\/i>aliases). I like to navigate to a directory, then use dir<\/b> <\/i>to produce a list of that directory, and then decide which directory to explore next. This technique is shown here.<\/p>\n To move up a level in the hierarchy, I use the cd<\/b> <\/i>alias with a couple of dots as shown here.<\/p>\n Cd ..<\/p>\n Join me tomorrow when I will talk about more cool Windows PowerShell and SQL Server 2012 stuff as I explore a specific SQL Server instance in the hierarchy.<\/p>\n Note <\/b>For more information about using Windows PowerShell with SQL Server (multiple versions) refer to this collection of Hey, Scripting Guy! Blog posts<\/a>.<\/p>\n I invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. See you tomorrow. Until then, peace.<\/p>\n Ed Wilson, Microsoft Scripting Guy<\/b> <\/p>\n","protected":false},"excerpt":{"rendered":" Summary: Microsoft Scripting Guy, Ed Wilson, provides an introduction to the SQL Server 2012 provider for Windows PowerShell. Microsoft Scripting Guy, Ed Wilson, is here. One of the really cool things about SQL Server 2012 is the SQL Server provider for Windows PowerShell. By using the SQL Server provider, you gain access to a driver that permits […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[146,3,176,375,45],"class_list":["post-4809","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-databases","tag-scripting-guy","tag-sql-server","tag-sql-server-2012","tag-windows-powershell"],"acf":[],"blog_post_summary":" Summary: Microsoft Scripting Guy, Ed Wilson, provides an introduction to the SQL Server 2012 provider for Windows PowerShell. Microsoft Scripting Guy, Ed Wilson, is here. One of the really cool things about SQL Server 2012 is the SQL Server provider for Windows PowerShell. By using the SQL Server provider, you gain access to a driver that permits […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/4809","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=4809"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/4809\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=4809"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=4809"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=4809"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Creating the remote Windows PowerShell console session<\/h2>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/6866.hsg-10-18-12-01.png\" "\\"Image")
<\/a><\/p>\nExploring the SQL Server provider<\/h2>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/6545.hsg-10-18-12-02.png\" "\\"Image")
<\/a><\/p>\n![\"\"](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/8688.hsg-10-18-12-03.png\")
<\/a><\/p>\nNavigating the SQLSERVER drive<\/h2>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/1362.hsg-10-18-12-04.png\" "\\"Image")
<\/a><\/p>\n