Skip to content

Instantly share code, notes, and snippets.

Avatar

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
###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 / 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 / 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 / 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 / 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"
@nanoDBA
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
You can’t perform that action at this time.