Skip to content

Instantly share code, notes, and snippets.

@MikeFal
MikeFal / Set-Ubuntu-SolarizedDark.ps1
Created June 19, 2019 17:11
Setting Ubuntu Terminal to solarized light/dark
## Original Script Credit: Drew Furgiuele @pittfurg
## https://port1433.com/2019/06/17/using-the-windows-subsystem-for-linux/
## Color mapping found here: https://github.com/neilpa/cmd-colors-solarized
## Get the current installed console values
$consoleName = (get-childitem -path Registry::HKEY_CURRENT_USER\Console | Where-Object {$_.Name -like "*ubuntu*"}).pschildname
##Next, go out and get the current console item from the current user registry
Push-Location HKCU:\Console
$console = Get-ItemProperty -PATH ("Registry::HKEY_CURRENT_USER\Console\" + $consolename)
$sql='
SELECT
@@SERVERNAME as ServerName
,SERVERPROPERTY('ProductVersion') as SQLVersion
,db.name
,db.recovery_model_desc
,sum(mf.size/128.0) as DBTotalSizeMB
from sys.databases db
join sys.master_files mf on db.database_id = mf.database_id
left join LogBackupInfo lbi on db.name = lbi.database_name
#This ONLY works with AD users
$cred = Get-Credential
$sql = 'SELECT @@SERVERNAME;SELECT suser_name();'
Invoke-Command -ComputerName PICARD -Credential $cred -ScriptBlock {Invoke-Sqlcmd -ServerInstance PICARD -Query $using:sql}
#This is for SQL users
$cred = Get-Credential
$sql = 'SELECT @@SERVERNAME;SELECT suser_name();'
function Get-FreeSpace{
<#
.SYNOPSIS
Uses WMI to get capacity and freespace for all disks/mounts on a host.
.DESCRIPTION
Uses WMI Win32_Volume to query logical disks and provide drive size and usage for all
logical disks and mountpoints. If no parameter is given, localhost is used. Otherwise
the host name should be passed.
$sql = @('PICARD','RIKER')
$servers = @('KIRK','SPOCK','PICARD','RIKER','WORF','WESLEY')
$servers | Where-Object {$sql -contains $_}
Import-Module SqlServer,FailoverClusters
$PrimaryNode = 'PICARD'
$nodes = (Get-Cluster -Name $PrimaryNode | Get-ClusterNode).Name
$nodes | ForEach-Object {Get-SqlDatabase -ServerInstance $_} | Select-Object Parent,name,LastBackupDate,LastLogBackupDate | Out-GridView
#Import Module (Requires SSMS 16, July 2016)
Import-Module SqlServer
$ConnectionString = 'Server=SHION;Database=WideWorldImporters;trusted_connection=true'
$BillToCustomerList = Invoke-Sqlcmd -ConnectionString $ConnectionString -Query 'SELECT DISTINCT i.BillToCustomerID FROM Sales.Invoices as i;'
#Create Temp Testing Stored Procedure
$TesttingSproc = "IF (SELECT object_ID('temp_TestQuery')) IS NOT NULL
DROP PROCEDURE temp_TestQuery;
GO
$relocate = @()
$file = Get-ChildItem C:\Temp\WideWorldImporters.bak
$dbname = (Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "RESTORE HEADERONLY FROM DISK='$($file.FullName)';").DatabaseName
$dbfiles = Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "RESTORE FILELISTONLY FROM DISK='$($file.FullName)';"
foreach($dbfile in $dbfiles){
if($dbfile.Type -eq 'L'){
$newfile = Join-Path -Path 'E:\SQLServer\Log' -ChildPath $($dbfile.PhysicalName.SubString($dbfile.PhysicalName.LastIndexOf('\')))
} else {
$newfile = Join-Path -Path 'E:\SQLServer\Data' -ChildPath $($dbfile.PhysicalName.SubString($dbfile.PhysicalName.LastIndexOf('\')))
}
#backup your databases
#get a collection of databases
$dbs = Invoke-Sqlcmd -ServerInstance localhost -Database tempdb -Query "SELECT name FROM sys.databases WHERE database_id > 4"
#Get a formatted string for the datetime
$datestring = (Get-Date -Format 'yyyyMMddHHmm')
#loop through the databases
foreach($db in $dbs.name){
$dir = "C:\Backups\$db"
$InstanceName = 'localhost'
$smo = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
if($smo.Configuration.DefaultBackupCompression.ConfigValue -eq 0){
$smo.Configuration.DefaultBackupCompression.ConfigValue = 1
$smo.Configuration.Alter()
}
$smo.Configuration.DefaultBackupCompression