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
}

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.

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

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{

    }
}