Skip to content

Instantly share code, notes, and snippets.


nanoDBA nanoDBA

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
#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 / 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 / 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 '' -Destination $SSMS_filename;
& $SSMS_filename /install /passive
<# Thanks to @sqltoolsguy and his team for creating the links! #>
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 '' -Destination $PlanEXP_filename; & $PlanEXP_filename /install /passive /norestart
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.**
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 ) #>
$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 / 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:
$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 / 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
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 )
--"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."
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 / 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.