Sql Server Powershell

Change Sql Server Configuration Manager IP Address

To change the IP address in sql servers’ configuration manager, you can use the following code.  Of note, when I changed the IP address in configuration manager under IP1 manually and tested connections, it really didn’t make a lick of difference.  I could connect whether or not the IP1 value reflected the correct IP address or not.  But, having a different IP listed in the configuration manager as opposed to what it really is just feels dishonest.

Import-Module SqlPS -DisableNameChecking
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
cls

try{

    $srvName = "servername"
    $Instance = "mssqlserver"
    $urn = "ManagedComputer[@Name='$srvName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"

    $wmi = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $srvName
    $tcp = $wmi.GetSmoObject($urn);
    $tcp.IPAddresses["IP1"].IPAddressProperties["IpAddress"].Value = '10.10.1.1';
    $tcp.Alter();

}
catch{
    $_ | fl -Force
}

Powershell & Sql Server Single User Mode

Sometimes (nigh, always) when you have to start a sql server in single user mode (the ‘/m’ after the net start…), executing queries in the sqlcmd prompt can be a bit of a painful endeavor.  What I usually do is use powershell to stop the service, then restart the service in single-user mode and grab a serverconnection to it immediately in powershell_ise:

Import-Module SqlPS -DisableNameChecking
cls

Stop-Service -Name "MSSQL`$SQL2012" -Force
net start "MSSQL`$SQL2012" /m
$sqlcmd = New-Object Microsoft.SqlServer.Management.Common.ServerConnection '.\sql2012'
$sqlcmd.Connect();

I can then reference the $sqlcmd object in other tabs in powershell_ise to work in a more pleasant environment:

sqlcmd

Notice, I can’t connect in SSMS:

ssms

Just a helpful tip.

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