Invoke-Sqlcmd -ServerInstance sql01 -Database master -Query ‘SELECT name FROM sys.databases’ If you have existing T-SQL statements, you can simply wrap them inside of the Invoke-Sqlcmd cmdlet: The ForEach-Object cmdlet can be used to return the results for multiple SQL Servers: 'SQL01', 'SQL02' |įorEach-Object One of the things that makes PowerShell so powerful is that once you figure out how to perform a task for one item (one computer in this scenario), it’s easy to perform that same task for multiple items. You can also see in the previous example that when the SQLPS module is imported, it automatically changes your current location to the SQLSERVER PSDrive.ĭetermining the instances for the server named SQL01 is simple as shown in the following example: Get-ChildItem -Path 'SQLSERVER:\SQL\SQL01' The warnings wouldn’t hurt anything, but I prefer not to see them. There are a couple of cmdlets in the SQLPS module that use unapproved verbs (Encode-SqlName and Decode-SqlName) that will cause warnings to be generated if this optional parameter isn’t specified. Notice that the -DisableNameChecking parameter was specified in the previous example. Since the SQL Server 2014 client tools are installed on our workstation, there is a PowerShell module named SQLPS installed and you’ll need to start out by importing that module: Import-Module -Name SQLPS -DisableNameChecking This allows you to work with SQL Server as if it were a file system. One way of working with SQL Server in PowerShell is through the SQLServer PSDrive that’s created when the SQL PowerShell snap-in or module is imported. Working with SQL Server as if it were a file system Note: SQL Server 2008 R2 and prior versions of SQL Server that supported PowerShell use a snap-in and beginning with SQL Server 2012 a module is used instead. All of the examples shown are being performed on a Windows 8.1 Enterprise edition workstation with the SQL Server 2014 management tools installed and the RSAT (Remote Server Administration Tools) installed. One has SQL Server 2008 R2 installed and the other one has SQL Server 2014 installed, each one has multiple instances of SQL Server installed. In this scenario, you have two servers running the core installation (no-GUI) version of Windows Server 2012 R2. This article isn’t meant to be a deep dive, it’s meant to get you started thinking about how you could write your own PowerShell code to retrieve the specific information that you’re looking for from your SQL Servers. In this article, I’ll be discussing how PowerShell can be used to retrieve just about any information that you would want to know about modern versions of SQL Server that you currently have running in your environment. One of those products is Microsoft SQL Server which I’ve been supporting since version 6.5 back in the 1990s. I’m an infrastructure guy who supports many different products at multiple datacenters in an enterprise environment.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |