Sql Server Powershell

Synchronize Sql Users and Logins with Powershell

I have a previous post about syncing users and logins via powershell, but seeing as that one uses the soon to be deprecated (if not already…no listing for this proc for sql server 2014 on MSDN…) ‘sp_change_users_login’, I thought I’d re-do it to be current.  Use at your own risk.

Keep in mind, a database login can indeed be set to use a different server login, so just bear that in mind if you find this not syncing all of them.  I don’t do that (and can’t imagine why anyone would want to make their lives more complicated by doing so….), so I’ve not coded for that scenario.  This also looks for system logins with ‘##’ at the beginning and just skips those.

#requires -module SqlPS

Import-Module SqlPS -DisableNameChecking

$serverName = 'ServerName'

try{

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $serverName
    $logins = $srv.Logins | where{$_.LoginType -eq [Microsoft.SqlServer.Management.Smo.LoginType]::SqlLogin} | select -ExpandProperty Name
    $srv.Databases | %{
        $dbName = $_.Name
        $sb = New-Object System.Text.StringBuilder
        $_.Users | where{$_.Name -in $logins} | %{
            if($_.Name -like '##*'){return};
            $sb.AppendLine("ALTER USER $($_.Name) WITH LOGIN = $($_.Name);") | Out-Null
        }
        $_.ExecuteNonQuery($sb.ToString());
    }
}
catch{
    $_ | fl -Force
}

Copy a Sql Server job via Powershell

This function will copy a job from one server to another using powershell. 

function Copy-SqlJob{
    [cmdletbinding(SupportsShouldProcess=$true)]
    param(
        [Parameter(Mandatory=$true)]
        $ComputerName,
        [Parameter(Mandatory=$true)]
        $Destination,
        [Parameter(Mandatory=$true)]
        $JobName
    )
    begin{
        Import-Module SqlPS -DisableNameChecking
    }
    process{

        if($PSCmdlet.ShouldProcess($ComputerName)){

            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Destination
            $job = $srv.JobServer.Jobs | where{$_.name -eq $JobName}
            if($job -ne $null){
                throw "Job $JobName already exist on server $Destination"
            }

            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Destination
            if($srv.JobServer.Jobs | where{$_.Name -eq $JobName}){
                throw "Job $JobName already exists on server $Destination"
                return;
            }

	        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $ComputerName
	        $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($srv)
	        $scriptr.Options.DriAll = $True
	        $scriptr.Options.IncludeHeaders = $True

            $job = $srv.JobServer.jobs | where{$_.Name -eq $JobName} 
            if($job -eq $null){
                throw "Job $JobName was not found on server $ComputerName."
                return;
            }
            $strJob = $scriptr.Script($job) | Out-String
        
            Invoke-Sqlcmd -ServerInstance $Destination -Database 'msdb' -Query $strJob
        }
        else{
            Write-Host "Copying Sql Job $JobName from $ComputerName to $Destination"
        }
    }
    end{

    }
}

Get-AdUser Group Membership

Quick snippit to grab a users’ group membership:

Get-ADUser -filter{samaccountname -eq 'snewman'} -Properties MemberOf | select -ExpandProperty MemberOf | %{$_.ToString().Split(',')[0].Replace('CN=', '');}

Timeouts in SqlPS Backup-SqlDatabase & Restore-SqlDatabase

Often when backing up a big database using SqlPS’s Backup-SqlDatabase cmdlet you’ll find yourself hitting a timeout after 600 seconds (10 minutes).  In order to alleviate this, you’ll need to pass in an SMO Server object to the Backup-SqlDatabase instead of defining the –ServerInstance:

try{

    $srv = new-object Microsoft.SqlServer.Management.Smo.Server "MyServerName"
    $srv.ConnectionContext.StatementTimeout = 0
    Backup-SqlDatabase -InputObject $srv -Database "MyBigDatabase" -BackupAction Database -BackupFile "f:\Backups\MyBigDatabase.bak" -CopyOnly -CompressionOption On 

}
catch{
    $_ | fl -Force
}

The same trick *should* work (as I’ve not tried it yet) via the Restore-SqlDatabase, as it also takes an –InputObject of type smo server as well.  This was supposedly fixed in Sql Server 2012 SP2, but if you have just the SqlCLR, SharedManagement, & PowerShell tools installed installed sans sql server, it can be an un-necessary pain to have to apply a service pack just to fix this issue.

Call RoboCopy via Powershell

Just a quick function I knocked up to copy files via RoboCopy in powershell.  If the destination directory does not exist, the function will create it.  Use at your own risk.

function Copy-RoboCopy{
    [cmdletbinding()]
    param(
        [Parameter(Mandatory)]
        [ValidateScript({[System.IO.Directory]::Exists($_);})]
        [string]$SourceDir,
        [Parameter(Mandatory)]
        [string]$Destination
    )
    begin{

    }
    process{

        if(!(Test-Path $Destination -PathType Container)){
            New-Item -ItemType Directory -Path $Destination -Force
        }

        if(!(test-path -Path "C:\Windows\System32\robocopy.exe" -PathType Leaf)){
            throw "Robocopy is not installed."
        }

        robocopy "$SourceDir" "$Destination" *.* /S /MT:32 /XJ /R:25 /W:5 /NP /XX 

    }
    end{

    }
    
}

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
    }
}

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{

    }
}