Skip to content

Instantly share code, notes, and snippets.

@SQLvariant
Created March 14, 2019 01:45
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save SQLvariant/8c06509c13c6b311ac67164b685b260c to your computer and use it in GitHub Desktop.
Common SQL PowerShell examples for the SqlServer module
<# 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