March 11th, 2019

Invoke-Sqlcmd is Now Available Supporting Cross-Platform

Steve Lee
Principal Software Engineer Manager

The official SqlServer module now includes a version of the Invoke-Sqlcmd cmdlet that runs in PSCore 6.2 and above. The version of the SqlServer module which contains this cmdlet is 21.1.18095-preview and is available in the PowerShell Gallery. In order to install this preview version of the module, you must run Install-Module (or Update-Module if you already have it installed) with the -AllowPrerelease. (Without that parameter you will receive the previous version of the module.)

Install-Module -Name SqlServer -AllowPrerelease

Why is Invoke-Sqlcmd Important

Invoke-Sqlcmd is probably the most crucial cmdlet in the SQL PowerShell space because of its broad capabilities. This cmdlet has a large number of parameters and can serve as a bridge when cmdlets are not yet available.

For example, the Backup-SqlDatabase is already available in PSCore. However as of this writing, the Restore-SqlDatabase cmdlet is not yet available. Without the Invoke-Sqlcmd cmdlet, you would either have to exit PowerShell, or rely on borderline-cryptic SMO code in order to restore your database. That is not a good situation to be in if you’re striving for consistency in your environment.

However, with the Invoke-Sqlcmd cmdlet’s ability to run any valid T-SQL command, you can bridge the gap of the missing Restore-SqlDatabase cmdlet by passing in a RESTORE DATABASE command in the -Query parameter of Invoke-Sqlcmd.

What is Invoke-Sqlcmd

If you don’t spend you working life in the SQL Server world, or you’re just new to SQL Server, you might not even know what Invoke-Sqlcmd is in the first place.

From the Help file: “The Invoke-Sqlcmd cmdlet runs a script containing the languages and commands supported by the SQL Server SQLCMD utility.” In other words, you’re getting a lot of PowerShell object-goodness, but it’s really being handed over to the database engine by a much older, very stable, utility.

What Can You Do With Invoke-Sqlcmd

Since Invoke-Sqlcmd has already been around for over a decade, there are already many examples for how you can leverage Invoke-Sqlcmd. However, one of the most important things to know about Invoke-Sqlcmd is that you can output the rows of data that are returned as a single object, in the form of a .Net DataTable. This allows for high speed transfer of data between servers when combined with the Write-SqlTableData cmdlet. Just add the -OutputAs parameter and specify DataTables.

As just one example, consider the situation where you want to collect information about the amount of disk space used by each data file across all of your databases on ServerA, but you want to store that information on Server B. The example below shows you how easy it is to pipe all of the rows from the query and pipe them directly into a table on ServerB.

Invoke-Sqlcmd -ServerInstance ServerA -DatabaseName master -OutputAs DataTables -Query "
SELECT @@ServerName AS 'ServerName'
            , DB_NAME(dbid) AS 'DatabaseName'
            , name AS 'LogicalName'
            , GETDATE() AS 'CheckDate'
            , CONVERT(BIGINT, size) * 8 AS 'SizeInKB'
            , filename AS 'DBFileName'
            , SYSDATETIMEOFFSET() 'DiscoveryOccured'
  FROM master..sysaltfiles
 WHERE dbid != 32767" |
Write-SqlTableData -ServerInstance ServerB -DatabaseName BlankDB -SchemaName dbo -TableName DatabaseSizeHistory -Force;

Again, since Invoke-Sqlcmd is outputting a .Net DataTable this allows the Write-SqlTableData cmdlet to write the rows at high speed using the SqlBulkCopy method. Also of note: By using the -Force parameter, it the table doesn’t exist yet, the Write-SqlTableData cmdlet will create it for you.

Multi-Server Operations

While the above code is certainly very useful for anyone who works with SQL Server, it gets even better. One of the other updates in v21.1.18095-preview of the SqlServer module is that Registered Server / CMS are now available in the SQL Provider in PSCore.

dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse
Mode Name
---- ----
-    localhost\SQL2016
-    localhost\SQL2017
-    ServerA
-    ServerB

When you combine the SQLRegistrations with Invoke-Sqlcmd or any other cmdlet that accepts a -ServerInstance parameter, you very easily multiply what you’re doing across many instances and/or databases.

In the example below, by getting making a call to the SQLRegistration provider, you get back a list of all the SQL Server Instances you have added to your Registered Servers. You can then pipe this list to foreach and provide the name of the instance to the -ServerInstance parameter of the Invoke-Sqlcmd cmdlet in the form of a pipeline property $_.Name. However, since we want all results written back to a single table, we don’t pass that pipeline property to Write-SqlTableData cmdlet, and instead provide it with a fixed SQL Server Instance to write to.

dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse |
WHERE {$_.Mode -ne 'd' } |
    foreach {
    Invoke-Sqlcmd -ServerInstance $_.Name -Database master -OutputAs DataTables -Query "
    SELECT @@ServerName AS 'ServerName',
           DB_NAME(dbid) AS 'DatabaseName',
           name AS 'LogicalName',
           GETDATE() AS 'CheckDate',
           CONVERT(BIGINT, size) * 8 AS 'SizeInKB',
           filename AS 'DBFileName',
           SYSDATETIMEOFFSET() 'DiscoveryOccured'
      FROM master..sysaltfiles
     WHERE dbid != 32767" |
    Write-SqlTableData -ServerInstance ServerB -DatabaseName BlankDB -SchemaName dbo -TableName DatabaseSizeHistory -Force;
    }

You can leverage this capability to do a lot of other tasks as well. Deploying code, collecting error log information, auditing your database user security, and searching your SQL Servers for object references are just a few examples.

Please Report Issues

You will probably find issues with the Invoke-Sqlcmd cmdlet in PSCore. Please report them by commenting on the PowerShell Gallery page for the SqlServer module.

The SQL Server Tools Team has automated test scripts that they run every time they make a change to the code base. However, since the Invoke-Sqlcmd cmdlet is being opened up to a completely new user base, it’s understandable that they are probably still building new test cases.

If you do find issues, please report them by commenting on the PowerShell Gallery page for the SqlServer module. Include as much of the code and error message as you can, but be sure to remove any sensitive information. Please make sure to use an account tied to a real email address so that you can be notified when the SQL Server Tools Team replies to your comment.

If the SQL Server Tools Team is able to reproduce the issue you reported, they may ask you to open an item on aka.ms/sqlfeedback. This will help them keep track of the issue and allow others customers who are running into the same issue to provide their feedback as well.

Additional Information:

Installing the PSCore Preview on Your OS

For instructions on how to install the preview version of PowerShell on your OS, see the official documentation.

Capabilities of Invoke-Sqlcmd on PSCore 6.2

The initial release of the Invoke-Sqlcmd cmdlet in PSCore does not include all of the parameters and capabilities of the Invoke-Sqlcmd cmdlet in Windows PowerShell. Specifically “-Variable-DisableVariables-DisableCommand-QueryTimeout-ConnectionTimeout-Hostname not supported yet.”

The 58 cmdlets Currently Available

While the SqlServer module currently has 106 cmdlets available in Windows PowerShell, only 58 of those 106 are available in PSCore. In the interests of making the current capabilities of the SqlServer module on PSCore more widely known, a full list of 58 current cmdlets is included below.

Get-Command -Module SqlServer -CommandType Cmdlet
CommandType     Name                                               Version    Source
-----------     ----                                               -------    ------
Cmdlet          Add-SqlAvailabilityDatabase                        21.1.18095 SqlServer
Cmdlet          Add-SqlAvailabilityGroupListenerStaticIp           21.1.18095 SqlServer
Cmdlet          Add-SqlColumnEncryptionKeyValue                    21.1.18095 SqlServer
Cmdlet          Add-SqlLogin                                       21.1.18095 SqlServer
Cmdlet          Backup-SqlDatabase                                 21.1.18095 SqlServer
Cmdlet          Convert-UrnToPath                                  21.1.18095 SqlServer
Cmdlet          ConvertFrom-EncodedSqlName                         21.1.18095 SqlServer
Cmdlet          ConvertTo-EncodedSqlName                           21.1.18095 SqlServer
Cmdlet          Get-SqlAgent                                       21.1.18095 SqlServer
Cmdlet          Get-SqlAgentJob                                    21.1.18095 SqlServer
Cmdlet          Get-SqlAgentJobHistory                             21.1.18095 SqlServer
Cmdlet          Get-SqlAgentJobSchedule                            21.1.18095 SqlServer
Cmdlet          Get-SqlAgentJobStep                                21.1.18095 SqlServer
Cmdlet          Get-SqlAgentSchedule                               21.1.18095 SqlServer
Cmdlet          Get-SqlBackupHistory                               21.1.18095 SqlServer
Cmdlet          Get-SqlColumnEncryptionKey                         21.1.18095 SqlServer
Cmdlet          Get-SqlColumnMasterKey                             21.1.18095 SqlServer
Cmdlet          Get-SqlCredential                                  21.1.18095 SqlServer
Cmdlet          Get-SqlDatabase                                    21.1.18095 SqlServer
Cmdlet          Get-SqlErrorLog                                    21.1.18095 SqlServer
Cmdlet          Get-SqlInstance                                    21.1.18095 SqlServer
Cmdlet          Get-SqlLogin                                       21.1.18095 SqlServer
Cmdlet          Get-SqlSmartAdmin                                  21.1.18095 SqlServer
Cmdlet          Grant-SqlAvailabilityGroupCreateAnyDatabase        21.1.18095 SqlServer
Cmdlet          Invoke-Sqlcmd                                      21.1.18095 SqlServer
Cmdlet          Join-SqlAvailabilityGroup                          21.1.18095 SqlServer
Cmdlet          New-SqlAvailabilityGroup                           21.1.18095 SqlServer
Cmdlet          New-SqlAvailabilityGroupListener                   21.1.18095 SqlServer
Cmdlet          New-SqlAvailabilityReplica                         21.1.18095 SqlServer
Cmdlet          New-SqlBackupEncryptionOption                      21.1.18095 SqlServer
Cmdlet          New-SqlCngColumnMasterKeySettings                  21.1.18095 SqlServer
Cmdlet          New-SqlColumnMasterKey                             21.1.18095 SqlServer
Cmdlet          New-SqlCredential                                  21.1.18095 SqlServer
Cmdlet          New-SqlCspColumnMasterKeySettings                  21.1.18095 SqlServer
Cmdlet          New-SqlHADREndpoint                                21.1.18095 SqlServer
Cmdlet          Read-SqlTableData                                  21.1.18095 SqlServer
Cmdlet          Read-SqlViewData                                   21.1.18095 SqlServer
Cmdlet          Remove-SqlAvailabilityDatabase                     21.1.18095 SqlServer
Cmdlet          Remove-SqlAvailabilityGroup                        21.1.18095 SqlServer
Cmdlet          Remove-SqlAvailabilityReplica                      21.1.18095 SqlServer
Cmdlet          Remove-SqlColumnEncryptionKey                      21.1.18095 SqlServer
Cmdlet          Remove-SqlColumnEncryptionKeyValue                 21.1.18095 SqlServer
Cmdlet          Remove-SqlColumnMasterKey                          21.1.18095 SqlServer
Cmdlet          Remove-SqlCredential                               21.1.18095 SqlServer
Cmdlet          Remove-SqlLogin                                    21.1.18095 SqlServer
Cmdlet          Resume-SqlAvailabilityDatabase                     21.1.18095 SqlServer
Cmdlet          Revoke-SqlAvailabilityGroupCreateAnyDatabase       21.1.18095 SqlServer
Cmdlet          Set-SqlAvailabilityGroup                           21.1.18095 SqlServer
Cmdlet          Set-SqlAvailabilityGroupListener                   21.1.18095 SqlServer
Cmdlet          Set-SqlAvailabilityReplica                         21.1.18095 SqlServer
Cmdlet          Set-SqlAvailabilityReplicaRoleToSecondary          21.1.18095 SqlServer
Cmdlet          Set-SqlCredential                                  21.1.18095 SqlServer
Cmdlet          Set-SqlErrorLog                                    21.1.18095 SqlServer
Cmdlet          Set-SqlHADREndpoint                                21.1.18095 SqlServer
Cmdlet          Set-SqlSmartAdmin                                  21.1.18095 SqlServer
Cmdlet          Suspend-SqlAvailabilityDatabase                    21.1.18095 SqlServer
Cmdlet          Switch-SqlAvailabilityGroup                        21.1.18095 SqlServer
Cmdlet          Write-SqlTableData                                 21.1.18095 SqlServer

Aaron Nelson ( blog | twitter ) is a Microsoft MVP for SQL Server (Data Platform) and leads the PowerShell Virtual Chapters of PASS, and volunteers for the local PASS Chapter AtlantaMDF, and helps organize SQL Saturday events in Atlanta. The PowerShell VC of PASS hosts monthly sessions on SQL Server and PowerShell, and you can find the recordings of those sessions on their YouTube channel.

Category
PowerShell

Author

Steve Lee
Principal Software Engineer Manager

Principal Software Engineer Manager PowerShell 7, PowerShellGet, PSScriptAnalyzer, VSCode-PowerShell extension, PowerShellEditorServices, etc...

0 comments

Discussion are closed.