Sql Server Powershell

NetBackup BPList via Powershell

Quick post on how to get netbackup information back using BPList and splitting the image information to powershell objects.  You can get more information from BPList using the –l argument such as size, account, etc… but I’ve just not coded that up yet.  The parameters for this default to the last 24 hours.  Use at your own risk.

function Get-SqlNetBackups{
    param(
        [Parameter(Mandatory=$true, ValueFromPipeline=$true)]
        [string]$ServerName,
        [Parameter(Mandatory=$false, ValueFromPipeline=$false)]
        [DateTime]$Start = (get-date).AddDays(-1),
        [Parameter(Mandatory=$false, ValueFromPipeline=$false)]
        [DateTime]$End = (Get-Date)
    )

    #HKEY_LOCAL_MACHINE\SOFTWARE\VERITAS\NetBackup\CurrentVersion
    $installDir = Get-ItemProperty "HKLM:\SOFTWARE\VERITAS\NetBackup\CurrentVersion" | select -expandproperty INSTALLDIR
    if($installDir -eq $null){
        throw "Netbackup is not installed."
    }

    $bpList = "$installDir\NetBackup\bin\bplist.exe"
    if(!(Test-Path $bpList -PathType Leaf)){
        throw "BPList was not found at $bpList.  This cmdlet uses (abuses) BPList to "
    }
    #Push-Location
    sl "$([System.IO.Path]::GetDirectoryName($bpList))"
    $objects = @();

    try{

        $fStart = $Start.ToString("MM/dd/yyyy")
        $fEnd = $End.ToString("MM/dd/yyyy")
        $type = ""

        $ErrorActionPreference = "stop";
        <#
            -l to show file details, the following is the format with -l.  We can get the size and account, which is nice. When I find the time I'll implement...
            -rw------- SQLAcctSe SQLAcctSe     2949120 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
            -rw------- SQLAcctSe SQLAcctSe           0 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
            -rw------- SQLAcctSe SQLAcctSe           0 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
            -rw------- SQLAcctSe SQLAcctSe           0 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
            -rw------- SQLAcctSe SQLAcctSe           0 Mar 02 00:04 SEAPR1DB0051.MSSQL7.SEAPR1DB0051.trx.p0013510cd7b_620.~.7.001of001.20160302000354..C:\
        #>
        $list = .\bplist -C $ServerName -t 15 -S seaveritas247 -s $fStart -e $fEnd -R \      

        $fmtString = "yyyyMMddHHmmss"
          
        $list -split "`r`n" | select -Unique | %{

            $backupType = ""
            $arr = $_.Split(".");  #the image string...

            switch($arr[3]){  #0-based, so 4th item 1-based...used to tell the backup type...
                "db"{
                    $backupType = "Full"
                }
                "trx"{
                    $backupType = "TranLog"
                }
                "inc"{
                    $backupType = "Diff"
                }
                default{
                    $backupType = "Other"
                }
            }

            $objects += [PSCustomObject]@{
                ServerName = $arr[0]
                DatabaseName = $arr[4] 
                Date = [DateTime]::ParseExact($arr[8], $fmtString, $null)
                Type = $backupType
                Image = $_
            } 
    
        }

        $objects; 
    }
    catch{
        $_ | fl -Force
    }
}
Advertisements

Powershell Central Management Server Recursive Server List

I had a need to recursively iterate my Central Management Server, yet always return the topmost GroupName.  The function takes an array of strings to return that correlate to the names of the first level groups.  Use at your own risk.

#requires -Module SqlPS 
#requires -Version 5

Import-Module SqlPS -DisableNameChecking

function Get-CMSServers{
    param(
        [string[]]$CMSGroup = @('Group1', 'Group2')  #default groups...
    )

    function Get-RegisteredServers{  
        param(
            $ServerGroup,
            $ParentGroup
        )

        $ServerGroup.RegisteredServers | %{
            $objects.Add([PSCustomObject]@{
                ServerName = $_.ServerName
                GroupName = $ParentGroup 
                GroupDescription = $_.Description 
                ParentName = $ServerGroup.Name
            }) | Out-Null
        }

        if($ServerGroup.ServerGroups.Count -gt 0){
            $ServerGroup.ServerGroups | %{
                Get-RegisteredServers -ServerGroup $_ -ParentGroup $ParentGroup
            }
        }
    }

    $objects = New-Object System.Collections.ArrayList
    Set-Variable -Name objects -Option AllScope

    try{

        $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection &quot;ServerName&quot;
        $ServerStore = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore $srvConn
        $ServerStore.DatabaseEngineServerGroup.ServerGroups | where{$_.Name -in $CMSGroup} | %{
            Get-RegisteredServers -ServerGroup $_ -ParentGroup $_.Name 
        }

        return $objects;
    }
    catch{
        $_ | fl -Force
    }
    finally{
        $srvConn.Disconnect();
    }

}

Get-CMSServers -CMSGroup @('Production', 'Reporting')

Add Domain User to Local Admin Group via DSC

Toying with DSC.  Here is some code to add a user to a local administrators group via DSC.  Use at your own risk.  Marginally tested (works on my machine…).

cls

configuration UserConfig{
    param(
        [System.Management.Automation.PSCredential]$DomainCredential
    )

    Import-DscResource -ModuleName PSDesiredStateConfiguration    

    node $AllNodes.NodeName{

        Group Admin{
            GroupName = 'Administrators'
            Ensure = 'Present'
            #PsDscRunAsCredential = $mycreds
            Credential = $DomainCredential
            Members = @('domainname\username')
        }

    }
}

$configData = @{
    AllNodes = @(
        @{
            NodeName = 'SEAPR1DBBAT046'
            PSDscAllowPlainTextPassword = $true
            PSDscAllowDomainUser = $true
        }
    )
}

$cred = Get-Credential -UserName domain\user -Message "Password please"
UserConfig -DomainCredential $cred -ConfigurationData $configData

Windows Live Writer

More for me than you.  If you like using windows live writer, the link to download it is now here.

Set Sql Server Configuration Property

Quick example function on how to set a sql server configuration property.

function Set-SqlConfigValue{
    [cmdletbinding(SupportsShouldProcess)]
    param(
        [string]$ServerName,
        [string]$ConfigName,
        [int]$ConfigValue
    )
    begin{
        Import-Module SqlPS -DisableNameChecking -Verbose:$false
    }
    process{

        Write-Verbose "Connecting to server $ServerName..."
        $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $ServerName

        try{
            $srvConn.connect();
            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

            Write-Verbose "Checking to see if $ConfigName is a valid property of the Sql Server Configuration..."
            $prop = $srv.Configuration | Get-Member -MemberType Property | where{$_.Name -eq $ConfigName}
            if($prop -eq $null){
                throw "Property $ConfigName is not a valid sql server property"
                return;
            }

            Write-Verbose "Altering configuration $ConfigName to $ConfigValue"
            $srv.Configuration.Properties[$ConfigName].ConfigValue = $ConfigValue;
            $srv.Alter();
        }
        catch{
            throw $_ 
        }
        finally{
            $srvConn.Disconnect();
        }

    }
    end{

    }
}

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
}