Sql Server Powershell

Get Backup Directory for all Sql Servers via Powershell

Quick script to spit out the backup directories for your sql servers. 

Import-Module SqlPS -DisableNameChecking

$objects = @();

try{

    gc -Path c:\Servers.txt | %{
        $srv = new-object Microsoft.SqlServer.Management.Smo.Server $_
        $objects += [PSCustomObject]@{
            ServerName = $srv.Name
            BackupDir = $srv.BackupDirectory
        }
    }

    $objects | Out-GridView
}
catch{
    $_ | fl -Force
}

Remove Database User & Login via powershell

Kind of a re-post of an older post of mine.  This one doesn’t use SQLPS though and it also drops the login from the server as well.  Use at your own risk.

Import-Module SqlPS -DisableNameChecking

$serverName = 'ServerName'
$loginToRemove = 'LoginName'
 
try{

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName
    $srv.Databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{

        foreach($user in $_.Users | where{$_.Name -eq $loginToRemove}){
            $user.Drop();
        }
    }
    foreach($login in $srv.Logins | where{$_.Name -eq $loginToRemove}){
        $login.drop();
    }
}
catch{
    $_ | fl -Force
}

Check Disk Offset using Powershell

Quick script to make sure your disk partitions are properly aligned for a sql server installation.  Read this article for disk partition best practices as to why you should always ensure that your offset is correctly set. 

cls

$objects = @();

@('Server1', 'Server2') | %{
	try{
        $srvName = $_
		Get-WmiObject -Class Win32_DiskPartition -ComputerName $srvName | %{
            $objects += [PSCustomObject]@{
                ServerName = $srvName
                DiskName = $_.Name
                StartOffset = $_.StartingOffset 
                Result = if(($_.StartingOffset % 4096) -eq 0){"Partitioned Correctly"}else{"ISSUE"}
            }
		}
	}
	catch{
		$_ | fl -Force
	}
}
$objects | Out-GridView

Set Server Default Backup Directory via Powershell

Quick script on how to set a sql servers’ default backup directory via powershell.  Use at your own risk.

#requires -module SqlPS
#requires -version 4

function Set-SqlDefaultBackupDirectory{
    [cmdletbinding(SupportsShouldProcess=$true)]
    param(
        [string]$ComputerName,
        [string]$BackupDirectory
    )
    begin{
        Import-Module SqlPS -DisableNameChecking
    }
    process{
        try{
            if($PSCmdlet.ShouldProcess($ComputerName)){
                $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $ComputerName
                $srv.BackupDirectory = $BackupDirectory
                $srv.Alter();
            }
            else{
                Write-Host "Setting the default backup directory on server $ComputerName to $BackupDirectory"
            }
        }
        catch{
            throw $_
        }
    }
    end{

    }
}

Get Objects in the *deny* roles in Sql Server via Powershell

Quick script to find all the objects (Users, Groups) in the *deny* roles in all databases.  Handy for when your AD account setups are heavily nested with duplicate users in multiple conflicting groups .  If you find yourself having to use deny in order to get permissions correct, it’s probably time you flattened out your groups a bit.  Using DENY can be a chore to track down.  This just spits out a gridview of the members of the *deny* roles.

Import-Module SqlPS -DisableNameChecking

$objects = @();
$servers = gc -Path c:\Servers.txt

try{

    $Servers | %{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
        $srv.Databases | where{$_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{
            $dbName = $_.Name 
            $_.Roles | where{$_.Name -like '*deny*'} | %{
                $roleName = $_.Name 
                $roleMembers = $_.EnumMembers()
                $roleMembers | %{
                    $objects += [PSCustomObject] @{
                        ServerName = $srvName
                        DatabaseName = $dbName
                        Role = $roleName
                        RoleMember = $_
                    }
                }
            }
        }
    }
    $objects | Out-GridView
}
catch{
    $_ | fl -Force
}

Change the sa password in Sql Server via Powershell

Here is how you change the sa password for your sql servers via powershell.  Use with caution, marginally tested.  Make sure your sa password is somewhat complex or this will throw an exception.

import-module SqlPS -DisableNameChecking
[string]$newPwd = 'C0mp13xP@55word%'


try{
    gc -Path c:\Servers.txt | %{

        $srvName = $_
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvName
        $srv.Logins | where{$_.Name -eq 'sa'} | %{
            $_.ChangePassword($newPwd);
        }
    }
}
catch{
    $_ | fl -Force
}

Create a Zero’d out File with Powershell

When sql server adds space to a transaction log file (or creates the transaction log file) it needs to zero-out said file.  Whilst zeroing out the file Sql Server is unfortunately read-only.  Hence why you need to be careful in setting reasonable transaction log growth sizes.  I wanted to get a quick fix on how long a particularly old system took to create a file and zero it out, hence this script. 

$start = get-date
$File = 'c:\fileToCreate.txt'

if(Test-Path $File){
    Remove-Item $File -Force
}
 
$arrSize= 64kb
$fileSize= 1GB
$buffer = new-object byte[]($arrSize)
$stream = [io.File]::OpenWrite($FilePath)

try{
    $size = 0
    while($size -lt $fileSize){
        $stream.Write($buffer, 0, $buffer.Length);
        $size += $buffer.Length;
    }
} 
finally{
    if($stream){
        $stream.Close();
    }
    $ts = New-TimeSpan -Start $start -End (Get-Date)
    $ts
 }

Collect Perfmon Data via Powershell

Quick script to collection permon data via powershell.  This will continuously spit out just the physical disk stats to a csv file (max 1GB) every 10 seconds.  Use at your own risk.  Barely tested.

function Get-PhysicalDisk{
    param(
        [Parameter(Mandatory=$true,ValueFromPipeline=$false)] 
        $ComputerName,
        [Parameter(Mandatory=$true,ValueFromPipeline=$false)] 
        $FileName
    )

    $outputs = @();
    $1GB = 1GB

    if(Test-Path $FileName){
        Remove-Item $FileName -Force
    }

    $physDiskCounters = Get-Counter -ComputerName $ComputerName -ListSet PhysicalDisk 
    Get-Counter -ComputerName $ComputerName -Counter $physDiskCounters.Paths -Continuous -SampleInterval 10 | Export-Counter -Path $FileName -FileFormat "CSV" -Circular -MaxSize $1GB -Force

}



I usually start this in a job on multiple servers via the Start-Job cmdlet so it will run in the background like so:

gc -Path C:\Servers.txt | %{
    $srvName = $_
    $fileName = "c:\Test\$($srvName)_PhysicalDisk.csv"

    $job = Start-Job {
        param(
            $compName,
            $fName
        )
        . "C:\PathToGetPhysicalDiskFile\Get-PhysicalDisk.ps1"
        Get-PhysicalDisk $compName $fName
    } -ArgumentList @($srvName, $fileName)
}

Add a linked server & remote login to Sql Server via Powershell

Sort of.  Trying to do this via just SMO and powershell was immensely frustrating when dealing with the remotelogin security.  What I wanted the login to do was to use a sql login no matter the context (the bottom-most radio button in the screenshot below).

image

 

Alas, every time I set the remotelogin property and called the SetRemotePassword() method it would automatically add it to the Local server login to remote server login mappings list.

image

Not what I want.  In order to determine the proper…properties to set I created a new linked server and scripted it out to have a gander at the properties.

image

Two properties stood out.  The @locallogin and the @useself.  Okay, cool.  Easy enough, the smo linkedserverlogin has a properties collection in it, should be easy enough to set.  Except, the bloody properties never get populated.  Even after calling the Initialize($true) on the LinkedServerLogin smo object, said properties are not there. 

Okay, let’s add them.  Nope.  There is no Add() method on the SqlPropertyCollection (or on the smo PropertyCollection inherited class).  In the end, I ended up just having to execute a sql call in order to get this to work, as I’d already spent 4 stinking hours trying to get this to work correctly.  I detest having to resort to doing this, but I HAVE to move on.

Here it is.  If you have this figured, please please let me know what the poop is.

Import-Module sqlps -DisableNameChecking

$srvName = 'ServerName'
$linkedServerLogin = 'LinkedServerLoginName'
$pwd = 'LinkedServerPassword'
$servers = gc -Path C:\LinkedServers.txt

cls

try{
    $srv = New-Object Microsoft.SqlServer.Management.smo.server $srvName
    $servers | %{
        if($srv.LinkedServers.Contains($_)){
            return;
        }
        
        $linkedServer = New-Object Microsoft.SqlServer.Management.Smo.LinkedServer 
        $linkedServer.Parent = $srv
        $linkedServer.Name = $_
        $linkedServer.DataSource = $_
        #$srv.LinkedServers.Add($linkedServer);
        $linkedServer.Create();

        $sql = "
        USE [master]
        GO
        EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'$($_)', @locallogin = NULL , @useself = N'False', @rmtuser = N'$($linkedServerLogin)', @rmtpassword = N'$($pwd)'
        GO
        "
        $srv.ConnectionContext.ExecuteNonQuery($sql);

    }
}
catch{
    $_ | fl -Force
}
finally{

}

Turn off AutoShrink on databases via powershell

Easy peasy script to turn off AutoShrink on all databases.  I’ll let Paul Randal do the talking as to why you shouldn’t have autoshrink turned on.

Import-Module sqlps -DisableNameChecking

$ignoreDBs = @('IgnoreDB')

try{
    gc -Path 'c:\Servers.txt' | %{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $_
        $srv.Databases | where{-not $_.IsSystemObject -and $_.Name -notin $ignoreDBs} | %{
            if($_.AutoShrink -eq $true){
                $_.AutoShrink = $false
                $_.Alter();
            }
        }        
    }
}
catch{
    $srvName
    $_ | fl -Force
}
finally{

}