MikeFal

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

MikeFal

$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

MikeFal

#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();'

MikeFal

$sql = @('PICARD','RIKER')
$servers = @('KIRK','SPOCK','PICARD','RIKER','WORF','WESLEY')
$servers | Where-Object {$sql -contains $_}

MikeFal

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.

MikeFal

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

MikeFal

#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

MikeFal

$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('\')))
    }

MikeFal

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

MikeFal

$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