Skip to content

Instantly share code, notes, and snippets.

nanoDBA / Sequential_Restores.ps1
Created Apr 30, 2019
Demo - PowerShell + DevOps Global Summit 2019 - Sequential Restores
View Sequential_Restores.ps1
# How will the backup headers be scanned? SQL Server and Backup Path
$BkParamHash = @{
SqlInstance = "localhost"
Path = "D:\Share01\Demo", "D:\Share02\Demo"
$BackupMetaData = Get-DbaBackupInformation @BkParamHash -Verbose
# Let's restore 11 databases
$DbSuffixRange = 500..510
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
nanoDBA / Get-SPIDs_last_batch.sql
Created May 31, 2019
Last_Batch timestamp - From "How to detect SqlServer connection leaks in a applications?"
View Get-SPIDs_last_batch.sql
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
View fix page verification by setting it to checksum in databases.sql
--An alternative to sp_msforeachdb
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 / 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
# 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 / 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 / arrayFromHere-String.ps1
Created May 4, 2021
Converting a Here-String to an Array of Strings
View arrayFromHere-String.ps1
$someThingsArray = ($HereString = @"
).split("`n").TrimEnd("`r") <# Converting a Here-String to an Array of Strings #>