Scott Newman Uncategorized

Connect to Sql Server under a different login context

Here’s how to connect to sql server under a different windows login context. If you pass in the login name as ‘domain\loginname’ it will indeed fail. Only pass in the login name.

 #omit the domain name in the login, or it will fail
$cred = Get-Credential -Message "Enter your login name" -UserName $env:USERNAME

try{

    $srv = New-Object Microsoft.sqlServer.Management.smo.Server "ServerName"

    $srv.ConnectionContext.LoginSecure = $true
    $srv.ConnectionContext.ConnectAsUser = $true

    $srv.ConnectionContext.ConnectAsUserName = $cred.username 
    $srv.ConnectionContext.ConnectAsUserPassword = $cred.GetNetworkCredential().Password 
    $srv.ConnectionContext.Connect() 
    $srv.ConnectionContext.ExecuteWithResults("SELECT SUSER_SNAME() AS CurrentUser").Tables[0] | ft -AutoSize
}
catch{
    $_ | fl -Force
}
finally{
    $srv.ConnectionContext.Disconnect();
}
Scott Newman Powershell, SQL Server

Enable Sql AlwaysOn

This always seems to give me trouble, and I always forget the manner in which I run this that works correctly, so, viola!

Invoke-Command -ComputerName $name -ScriptBlock{
        Import-Module SqlPS -DisableNameChecking

        $srv = New-Object Microsoft.SqlServer.Management.smo.Server "."
        Enable-SqlAlwaysOn -InputObject $srv -Force
        $srv.ConnectionContext.Disconnect()
    }

Scott Newman Powershell, SQL Server

Restore Database with SMO

Probably 3 or 4 versions of this already on here.  So here is one more.  

function Invoke-Restore{
    [cmdletbinding()]
    param(
        [Parameter(Mandatory)]
        [string]$ServerName,
        [Parameter(Mandatory)]
        [int]$Port,
        [string]$DatabaseName = $null,
        [string]$BackupFile = "c:\PathToBackupFile.bak",
        [string]$dataDir = "d:\mssql\data",
        [string]$logDir = "d:\mssql\tranlog",
        [switch]$Force
    )

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
 

    try{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$ServerName,$Port"
        $backup = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem ($BackupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
         
        $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
        $restore.Devices.Add($backup);
        $restore.NoRecovery = $false;
        $restore.ReplaceDatabase = $true
 
        $dbFiles = $restore.ReadFileList($srv);
        $header = $restore.ReadBackupHeader($srv)
        if([System.String]::IsNullOrWhiteSpace($DatabaseName)){
            $DatabaseName = $header.DatabaseName
        }
        $restore.Database = $DatabaseName
        if($Force -and $srv.Databases[$DatabaseName]){
            $srv.KillDatabase($DatabaseName);
        }

        foreach($dbFile in $dbFiles){
            $fileName = [System.IO.Path]::GetFileName($dbFile.PhysicalName);
            switch($dbFile.Type){
                'D'{
                    [void]$restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "$dataDir\$fileName")))
                }
                'L'{
                    [void]$restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "$logDir\$fileName")))
                }
            }
        }
    
        $srv.ConnectionContext.StatementTimeout = 0;
        $restore.SqlRestore($srv);
        $srv.ConnectionContext.Disconnect();

    }
    catch{
        $_ | fl -Force
    }

}

Scott Newman AlwaysOn, Powershell, SQL Server

Add an AvailabilityDatabase

function Add-AvailabilityGroupDatabase{
    param(
        $ServerName,
        $Port = 1433,
        $DatabaseName,
        $AvailabilityGroup
    )
    begin{

    }
    process{

        if(!$ServerName.Contains('.')){
            $ServerName = "$ServerName.$env:USERDNSDOMAIN"
        }
        $srv = new-object Microsoft.SqlServer.Management.smo.Server "$ServerName,$Port"
        $ServerName = $srv.AvailabilityGroups[$AvailabilityGroup].PrimaryReplicaServerName
        $srv.ConnectionContext.Disconnect()
        $srv = new-object Microsoft.SqlServer.Management.smo.Server "$($ServerName).$env:USERDNSDOMAIN,$Port"

        if(!$srv.Databases[$DatabaseName]){
            throw "Could not find a database $DatabaseName on primary replica $ServerName"
        }

        if(!$srv.AvailabilityGroups[$AvailabilityGroup].AvailabilityDatabases.Contains($DatabaseName)){
            [void]$srv.ConnectionContext.ExecuteNonQuery("RESTORE DATABASE [$DatabaseName] WITH RECOVERY;");
            $ADB = New-Object Microsoft.SqlServer.Management.smo.AvailabilityDatabase($srv.AvailabilityGroups[$AvailabilityGroup], $DatabaseName);
            $ADB.Create();
        }
        else{
            Write-Warning "Database $DatabaseName is already an availbility database on server $ServerName."
        }
        
        
        $srv.AvailabilityGroups[$AvailabilityGroup].AvailabilityReplicas | ?{$_.Name -ne $ServerName} | select -expandproperty Name | %{
            $name = $_ 
            try{
                $sec = new-object Microsoft.SqlServer.Management.smo.Server "$($name).$env:USERDNSDOMAIN,$Port"
                $ag = $sec.AvailabilityGroups[$AvailabilityGroup]
                if(!$ag.AvailabilityDatabases[$DatabaseName].IsJoined){
                    $ag.AvailabilityDatabases[$DatabaseName].JoinAvailablityGroup();
                }
                else{
                    Write-Warning "Database $DatabaseName is already an availbility database on server $name."
                }
            }
            catch{
                Write-Error $_ | fl -Force
            }
            finally{
                $sec.ConnectionContext.Disconnect();
            }
        }


        $srv.ConnectionContext.Disconnect()

    }
    end{

    }
}

Scott Newman Powershell, SQL Server, Uncategorized

Capture Sql Server Information Messages in Powershell

Sometimes you have a need to capture the informational messages generated in sql server, for example sp_help_revlogin only prints the user information to the messages tab.

Import-Module SqlServer -DisableNameChecking
cls
try {

$users = @('user1', 'user2')

$users | % {
$user = $_

$sqlConn = New-Object System.Data.SqlClient.SqlConnection "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=servername"
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
param(
$sender,
$event
)

Write-host $event.Message

};
$sqlConn.add_InfoMessage($handler);
$sqlConn.FireInfoMessageEventOnUserErrors = $true;
$sqlConn.Open();
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand("EXEC sp_help_revlogin '$user', NULL", $sqlConn);
$sqlcmd.CommandType = [System.Data.CommandType]::Text
[void]$sqlCmd.ExecuteNonQuery();
}

}
catch {
$_ | fl -Force
}
finally {
$sqlConn.Close();
}

Scott Newman Powershell, SQL Server

Get Index Key Length via Powershell

Yep.

function Get-IndexKeyWidth{
    param(
        [string]$ServerName,
        [int]$Port = 2080,
        [string]$DatabaseName,
        [string]$TableName
    )
    begin{

    }
    process{
        $objects = New-Object System.Collections.Generic.List[object]
        $indexes = Invoke-Sqlcmd -ServerInstance "$ServerName,$Port" -Database $DatabaseName -Query "EXEC sp_helpindex [$TableName]" 
        $indexes | %{
            $sb = New-Object System.Text.StringBuilder
            $indexName = $_.index_name
            $_.index_keys -split ',' | %{
                [void]$sb.Append("'$($_.ToString().Trim())',");
            }
            $cmd = "SELECT SUM(max_length)AS TotalIndexKeySize
                    FROM sys.columns
                    WHERE name IN ($($sb.ToString().Substring(0, $sb.ToString().Length-1)))
                    AND object_id = OBJECT_ID(N'$TableName');"
                    $cmd
            Invoke-Sqlcmd -ServerInstance "$ServerName,$Port" -Database $DatabaseName -Query $cmd | %{
                [void]$objects.Add([PSCustomObject]@{
                    ServerName = $ServerName
                    DatabaseName = $DatabaseName 
                    TableName = $TableName 
                    IndexName = $indexName
                    SizeBytes = $_.TotalIndexKeySize 
                });
            }
        }
        $objects 
    }
    end{

    }
}
Scott Newman Powershell, SQL Server

SqlAgent not Capturing Errors from Powershell

Some strange behavior I noticed with sql server agent not capturing thrown errors from powershell.  Take this code:

try{
    1/0
}
catch{
    Write-Output ($_ | fl -Force | Out-String)
    throw "failure"
}

This will most certainly error, and if you run it in powershell, yep:

exception

Right then, let’s call it from an agent job then via cmd line:

agentCmd

Looks good.  Let’s run it!

success

Uhhh….mkay….let’s check the history of said ‘successful’ job:

JobText

I certainly see the exception.  Why is the job returning successfully?

The answer?   

¯\_(ツ)_/¯

 

But, I do know how to get it to start throwing errors successfully.  The problem is with the –file argument to powershell in the command:

noFileArg

  Remove that, and viola:

fail

Job now shows as failed:

fail