Skip to content

Instantly share code, notes, and snippets.

Avatar

nanoDBA nanoDBA

View GitHub Profile
@nanoDBA
nanoDBA / Install-PowerlineFonts.ps1
Created Sep 10, 2021
Install Powerline fonts from GitHub - requires git be installed
View Install-PowerlineFonts.ps1
<# clone latest master #>
$gitHubLink = "https://github.com/powerline/fonts.git"
$gitHubFolderName = (split-path $gitHubLink -Leaf).Replace('.git','')
$targetFolder = "$env:USERPROFILE\downloads\Repos\$gitHubFolderName"
<# *** This path and all subdirs are about to be deleted - Are you sure? *** #>
If(Test-Path $targetFolder) {Remove-Item -Recurse -Force $targetFolder\* -Confirm}
If(!(Test-Path $targetFolder)) {mkdir $targetFolder }
git clone -b master $gitHubLink $targetFolder
Invoke-Item $targetFolder
@nanoDBA
nanoDBA / Invoke-QueryInParallel.ps1
Last active Aug 29, 2021
Run a query against multiple databases by querying groups of instances listed in the CMS in parallel, then output a spreadsheet
View Invoke-QueryInParallel.ps1
# Run a query against multiple databases
# by querying groups of instances
# listed in the CMS in parallel,
# then output a spreadsheet
#
# Modules used: PoshRSJob, dbatools, ImportExcel
#
# Stop & remove all PoshRSJobs - Are you sure you want to do this?
# Get-RSJob | Stop-RSJob; Get-RSJob | remove-rsjob
@nanoDBA
nanoDBA / Get-AllSqlAgentJobsInParallel.ps1
Created Aug 29, 2021
Find all the SQL Agent Jobs by querying groups of instances listed in the CMS in parallel using runspaces, then output a spreadsheet
View Get-AllSqlAgentJobsInParallel.ps1
# Find all the SQL Agent Jobs by querying groups
# of instances listed in the CMS
# in parallel, then output a spreadsheet
#
# Modules used: PoshRSJob, dbatools, ImportExcel
#
# Stop & remove all PoshRSJobs - Are you sure you want to do this?
# Get-RSJob | Stop-RSJob; Get-RSJob | remove-rsjob
# Use CMS server and optional group name to identify SQL instance names, remove duplicates
@nanoDBA
nanoDBA / arrayFromHere-String.ps1
Created May 4, 2021
Converting a Here-String to an Array of Strings
View arrayFromHere-String.ps1
$someThingsArray = ($HereString = @"
one
two
three
"@
).split("`n").TrimEnd("`r") <# Converting a Here-String to an Array of Strings https://web.archive.org/web/20201027204331/https://gallery.technet.microsoft.com/scriptcenter/Tip-of-the-Week-Converting-221aab3f #>
@nanoDBA
nanoDBA / process-insert.ps1
Last active Dec 11, 2020
Avoid running the query against instances that may be down and time out, as well as adding some output that can be seen when looking at job history when scheduling the PowerShell job using SQL Agent
View process-insert.ps1
# adapted from https://gist.github.com/potatoqualitee/20a3f84e987cafe03e0ebe3b4d593c65#file-process-insert-ps1
# Specify your servers
$servers = "sql2014","sql2012","sql2016","sql2017"
# Setup the T-SQL
$sql = "SELECT SERVERPROPERTY('ServerName') AS SqlInstance, login_name as [Login], [host_name] as Host,
DB_NAME(p.dbid) as [Database], s.[program_name] as Program, s.login_time as LoginTime
FROM sys.dm_exec_sessions s inner join sys.sysprocesses p on s.session_id = p.spid
WHERE p.dbid is not NULL
and DB_NAME(p.dbid) != 'tempdb'
@nanoDBA
nanoDBA / FakeClient.ps1
Created Dec 11, 2020
Impersonating an Application Name Using Connect-DbaInstance
View FakeClient.ps1
$cred = Get-Credential 'domain\someAccount'
$connFakeClient = Connect-DbaInstance -SqlInstance YOURSQLSRVR -SqlCredential $cred -ClientName "Test App"
Invoke-DbaQuery -SqlInstance $connFakeClient -Query "WAITFOR DELAY '00:02:00'" -MessagesToOutput
@nanoDBA
nanoDBA / Encryption of Credential with AES Key.ps1
Last active May 27, 2020
Passing Credentials in PowerShell between users and computers using keys
View Encryption of Credential with AES Key.ps1
## Create AES key with random data and export to file
<#
Sometimes a script needs to use a password, but you want it stored securely. Passing a credential by utilizing 2 things:
1. A password file that contains the encrypted file
2. A key file that contains a randomly generated AES key
allows for utilization of credentials within scripts by different users and servers.
**The following method is only as secure as the locations of the files(password and key) themselves.**
View fix page verification by setting it to checksum in databases.sql
--https://www.brentozar.com/blitz/page-verification/
--An alternative to sp_msforeachdb
--https://johnmccormack.it/2015/12/an-alternative-to-sp_msforeachdb/
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
SELECT name, 0 AS completed
INTO #tmp
FROM sys.databases AS s
WHERE s.page_verify_option_desc <> 'CHECKSUM'
@nanoDBA
nanoDBA / Get-SPIDs_last_batch.sql
Created May 31, 2019
Last_Batch timestamp - From "How to detect SqlServer connection leaks in a ASP.net applications?"
View Get-SPIDs_last_batch.sql
--Get-SPIDs_last_batch.sql
-- https://stackoverflow.com/questions/212596/how-to-detect-sqlserver-connection-leaks-in-a-asp-net-applications/12428235#12428235
SELECT S.spid, S.login_time, S.last_batch, S.status, S.hostname, S.program_name, S.cmd, S.loginame,
last_sql = ( select text from sys.dm_exec_sql_text(S.sql_handle) )
, database_name = DB_NAME(dbid)
FROM sys.sysprocesses S
INNER JOIN sys.dm_exec_sessions sx
on sx.session_id = S.spid
and sx.is_user_process = 1
@nanoDBA
nanoDBA / Practical PowerShell for Busy DBAs.ps1
Created Apr 30, 2019
Intro to PowerShell and dbatools for SQL Server DBAs (originally presented in August 2018 using the ISE)
View Practical PowerShell for Busy DBAs.ps1
#requires -version 5.0
#requires runasadministrator
#region overview
<#
'Practical PowerShell for The Busy DBA' – this will be hands-on (lab + presentation)
- Basic overview of PowerShell and getting started (WMF 5.1 and other versions: prereqs, cmdlets,
transcription, console navigation, and getting quick help/examples)
- Installing Modules with PowerShell 5.0 and up