Created
March 14, 2019 01:45
Star
You must be signed in to star a gist
Common SQL PowerShell examples for the SqlServer module
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<# Get the Server SMO objects for ServerA & ServerB | |
The default output for this command will include the Instance name, version, Service Pack, & CU Update Level #> | |
Get-SqlInstance -ServerInstance ServerA, ServerB | |
<# Sample Output #> | |
Instance Name Version ProductLevel UpdateLevel | |
------------- ------- ------------ ----------- | |
ServerA 13.0.5233 SP2 CU4 | |
ServerB 14.0.3045 RTM CU12 | |
<# Get the Instance name, version, Service Pack, & CU Update Level, | |
for all SQL Server instances listed in your Registered Servers file. #> | |
Dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse | | |
WHERE {$_.Mode -ne 'd' } | | |
foreach { | |
Get-SqlInstance -ServerInstance $_.Name | |
} | |
<# Sample Output #> | |
Instance Name Version ProductLevel UpdateLevel | |
------------- ------- ------------ ----------- | |
localhost\SQL2016 13.0.1742 RTM n/a | |
localhost\SQL2016Happy 13.0.5233 SP2 CU4 | |
localhost\SQL2017 14.0.3045 RTM CU12 | |
<# Retrieve a list of Databases on the SQL2017 instance of the local machine. #> | |
Get-SqlDatabase -ServerInstance localhost\SQL2017 | |
<# Sample Output #> | |
Name Status Size Space Recovery Compat. Owner | |
Available Model Level | |
---- ------ ---- ---------- -------- ------- ----- | |
AdventureWorks2017 Normal 336.00 MB 57.01 MB Simple 140 sa | |
master Normal 6.00 MB 368.00 KB Simple 140 sa | |
model Normal 16.00 MB 5.53 MB Full 140 sa | |
msdb Normal 48.44 MB 1.70 MB Simple 140 sa | |
PBIRS Normal 144.00 MB 55.95 MB Full 140 sa | |
PBIRSTempDB Normal 16.00 MB 4.20 MB Simple 140 sa | |
SSISDB Normal 325.06 MB 26.21 MB Full 140 sa | |
tempdb Normal 72.00 MB 61.25 MB Simple 140 sa | |
WideWorldImporters Normal 3.2 GB 2.6 GB Simple 130 sa | |
<# Backup all databases on the Default instance of the Local machine #> | |
Get-SqlDatabase -ServerInstance localhost\SQL2017 | | |
Where { $_.Name -ne 'tempdb' } | | |
Backup-SqlDatabase -CompressionOption On | |
<# Retireves a list of all databases on the ServerB instance, then presents | |
a grid/table to select which databases should be backed up. Once the user | |
clicks on the "OK" button, only the highlighted databases will be backed up. #> | |
Get-SqlDatabase -ServerInstance ServerB | | |
Out-GridView -PassThru | | |
Backup-SqlDatabase -CompressionOption On | |
<# Deploys the [very popular] sp_whoisactive stored procedure to the master database | |
of every instance in your Registered Servers file. #> | |
Dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse | | |
WHERE {$_.Mode -ne 'd' } | | |
foreach { | |
Invoke-Sqlcmd -ServerInstance $_.Name -Database master -InputFile c:\temp\who_is_active_v11_32.sql | |
} | |
<# Add Demo to run a query against svery server or database. | |
of every instance in your Registered Servers file. #> | |
<###################################################################### | |
MISSING DEMO | |
#######################################################################> | |
Dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse | | |
WHERE {$_.Mode -ne 'd' } | | |
foreach { | |
Invoke-Sqlcmd -ServerInstance $_.Name -Database master -Query " | |
SSF | |
"| | |
Write-SqlTableData -ServerInstance localhost\SQL2017 -DatabaseName BlankDB -SchemaName Sales -TableName SalesOrderDetail -Force | |
} | |
<###################################################################### | |
/MISSING DEMO | |
#######################################################################> | |
<# Read & write data from one table to another. With the -Force parameter included, | |
the table will be created if it does not exist. #> | |
,(Read-SqlTableData -ServerInstance localhost\SQL2016 -DatabaseName AdventureWorks2016 -SchemaName Sales -TableName SalesOrderDetail) | | |
Write-SqlTableData -ServerInstance localhost\SQL2017 -DatabaseName BlankDB -SchemaName Sales -TableName SalesOrderDetail -Force | |
<# Retrieves all of the failed SQL Agent Jobs since Midnight on the Default & SQL2017 instances of the local machine. #> | |
Get-SqlAgentJobHistory -ServerInstance localhost, localhost\SQL2017 -Since Midnight -OutcomesType Failed | |
<# Reports every failed SQL Agent Job since Midnight, | |
for all SQL Server instances listed in your Registered Servers file. #> | |
Dir 'SQLSERVER:\SQLRegistration\Database Engine Server Group' -Recurse | | |
WHERE {$_.Mode -ne 'd' } | | |
foreach { | |
Get-SqlAgentJobHistory -ServerInstance $_.Name -Since Midnight -OutcomesType Failed | |
} | |
<# Find out the Default Backup, Data File, * Log File Directories #> | |
(Get-SqlInstance -ServerInstance localhost\SQL2017).BackupDirectory | |
(Get-SqlInstance -ServerInstance localhost\SQL2017).DefaultFile | |
(Get-SqlInstance -ServerInstance localhost\SQL2017).DefaultLog |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment