nanoDBA

$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

$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

# 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

--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

--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

#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

# 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

# Restoring Databases from Files in Parallel

# Let's restore 11, 21, 41 databases!
     $DbSuffixRange = 500..510
    # $DbSuffixRange = 500..520
    # $DbSuffixRange = 500..540

$RSJobparamHash = @{
    Throttle        = 8
    ModulesToImport = "dbatools"

nanoDBA

--How far behind is Secondary in AlwaysOn replica ? ( LAG )
--http://blogs.extremeexperts.com/2013/11/04/sql-server-alwayson-how-far-behind-is-secondary/
--"There are a lot of times customers ask me how much time is my secondary behind my primary?
--Such a simple question can be answered easily using some good DMVs."

SELECT AGS.name                       AS AGGroupName, 
       AGL.dns_name			          AS Listener_dns_name, 
       AGL.port				          AS Listener_port, 
       AGL.ip_configuration_string_from_cluster    AS Cluster_IP_addresses, 
       AR.replica_server_name         AS InstanceName, 

nanoDBA

<# Install Nuget for Allusers if it's not already there #>if (-not (Get-PackageProvider -Name NuGet -ListAvailable -ErrorAction:SilentlyContinue )) { Install-PackageProvider -Name NuGet -Force -Scope AllUsers }
# modified from https://gist.github.com/RobCannon/12f338f00ce906fb4d43ee472c6a1389