Invoke-Sqlcmd is Now Available Supporting Cross-Platform
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 -AllowPrereleaseWhy 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.
Light
Dark
0 comments