Skip to content

Instantly share code, notes, and snippets.

nanoDBA nanoDBA

Block or report user

Report or block nanoDBA

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
View GitHub Profile
View storage-space-creation_with_share_and_ACLS.ps1
## DANGER - this script is an example and will likely NUKE DELETE REMOVE DESTROY DATA
## If you run this on a prod system, it may be a résumé / CV generating event that could hurt businesses
###http://windowsitpro.com/windows-server-2012-r2/powershell-storage-space-creation-tips
#List all disks that can be pooled and output in table format (format-table)
Get-PhysicalDisk -CanPool $True | ft FriendlyName,OperationalStatus,Size,MediaType
get-disk 1,2
#### Initialize Disks to GPT
get-disk 1,2 | Initialize-Disk -PartitionStyle GPT
@nanoDBA
nanoDBA / future compatiblity level.sql
Created Jan 10, 2018
SQL Server knows your future compatiblity level... danger!
View future compatiblity level.sql
--Do you really reallly want to run this?
DECLARE @highestcompatlevel TINYINT,
@dbname NVARCHAR(128),
@sql NVARCHAR(400);
SELECT @highestcompatlevel = MAX(compatibility_level)
FROM sys.databases;
SELECT @dbname = DB_NAME();
SELECT @sql = 'ALTER DATABASE [' + @dbname + '] set compatibility_level = ' + CAST(@highestcompatlevel AS NVARCHAR(3));
@nanoDBA
nanoDBA / Get-SSMS.ps1
Last active Jan 4, 2019
SSMS latest version - download and install using PowerShell
View Get-SSMS.ps1
<# download and install latest SSMS release #>
$SSMS_filename = "$env:USERPROFILE\downloads\SSMS-Setup-ENU-" + ([string](Get-Date -format "yyyy-MM-dd")) + ".exe" ;
Start-BitsTransfer -Source 'https://aka.ms/ssmsfullsetup' -Destination $SSMS_filename;
& $SSMS_filename /install /passive
<# Thanks to @sqltoolsguy and his team for creating the aka.ms links!
https://twitter.com/sqltoolsguy/status/1011754064516804608 #>
@nanoDBA
nanoDBA / Install-S1PlanExplorer.ps1
Created Jul 17, 2018
Download and Install SentryOne Plan Explorer
View Install-S1PlanExplorer.ps1
<# download and install SentryOne Plan Explorer: 2018-07-16 release #>$PlanEXP_filename = "$env:USERPROFILE\downloads\PlanExplorerInstaller.exe"; Start-BitsTransfer -Source 'https://downloads.sentryone.com/downloads/sqlsentryplanexplorer/x64/PlanExplorerInstaller.exe' -Destination $PlanEXP_filename; & $PlanEXP_filename /install /passive /norestart
@nanoDBA
nanoDBA / Encryption of Credential with AES Key.ps1
Last active Aug 2, 2018
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.**
@nanoDBA
nanoDBA / ComparingSQLServerConfigs_sp_configure-need_to_turn_into_a_function.ps1
Last active Sep 5, 2018
Attempting to Compare sp_configure differences between two instances - TODO: need to change into a function
View ComparingSQLServerConfigs_sp_configure-need_to_turn_into_a_function.ps1
<# To compare two server configs ( sp_configure ) #>
# https://sqldbawithabeard.com/tag/sql-dba/
# https://dbatools.io/functions/get-dbaspconfigure/
$oldProps = Get-DbaSpConfigure -SqlInstance 'SERVER01' # | Sort-Object ConfigName
$newProps = Get-DbaSpConfigure -SqlInstance 'NEWSERVER-02' #| Sort-Object ConfigName
$propCompare = foreach ($prop in $oldprops) {
if (($prop2 = $newprops | Where-Object DisplayName -EQ $prop.DisplayName) -AND ($prop2.RunningValue -NE $prop.RunningValue)) {
@nanoDBA
nanoDBA / DANGER-logoff.ps1
Created Jan 30, 2019
It's a mess but it worked... I was able to query several servers concurrently to find where I was logged in via RDP and logoff my sessions.
View DANGER-logoff.ps1
#Requires -Modules PoshRSJob
break; #Safety First - These bits of script are meant to match a user name
# and LOGOFF the RDP sessions of that user when passed a list of servers
# So... potentially very dangerous
# source: https://4sysops.com/archives/how-to-force-a-user-logoff-remotely-with-powershell/
$results = Start-RSJob -Throttle 20 <#-ModulesToImport dbatools#> -InputObject $TargetServerstoKILL_RDP_SESSIONS -Verbose -ScriptBlock { $username = 'kill.this.username'; Write-Output "$($_)..."; $sessionId = 7777777; Write-Output "`$sessionId = $($sessionId)"; quser /server:"$($_)" | Where-Object { $_ -match $userName } ; $sessionId = ((quser /server:"$($_)" | Where-Object { $_ -match $userName }) -split ' +')[2] ; Write-Output "`$sessionId = $($sessionId)" ; IF ($sessionId -NE "SESSIONNAME" -AND $sessionId -NE $null -AND $_ -NE $($env:COMPUTERNAME)) { logoff $sessionId /server:"$($_)" } ELSE { Write-Output "No match found for $username" } } | Wait-RSJob -Timeout 72 -ShowProgress -Verbose | Receive-RS
@nanoDBA
nanoDBA / Install-NuGetProvider.ps1
Last active Feb 27, 2019
Install Nuget if it's not already there - haven't tested this yet
View Install-NuGetProvider.ps1
<# 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
@nanoDBA
nanoDBA / Get-AlwaysOn_Availability_Groups_Lag,info.sql
Created Apr 22, 2019
How far behind is Secondary in AlwaysOn replica ? ( LAG )
View Get-AlwaysOn_Availability_Groups_Lag,info.sql
--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
nanoDBA / Parallel_Restores.ps1
Created Apr 30, 2019
Demo - PowerShell + DevOps Global Summit 2019 - Parallel Restores
View Parallel_Restores.ps1
# 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"
You can’t perform that action at this time.