Category: Powershell

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{

    }
}

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

Restore Database to AvailabilityGroups

Code more for me than you.  Backup a database and restore it to an availability group (picks the AG that has the least number of databases).  If the server isn’t clustered or doesn’t have HADR enabled, just restores it.  Not thoroughly tested.

#Require -Version 5
#Require -Modules SqlPS, FailoverClusters, ActiveDirectory

function Deploy-Database{
    param(
        [Parameter(Mandatory=$true)]
        [string]$DestServer,
        [Parameter(Mandatory=$true)]
        [string]$SourceServer,
        [Parameter(Mandatory=$true)]
        [string]$SourceDatabase,
        [ValidateSet(2050,2060,2070,1433)]
        [int]$Port = 1433,
        [string]$BackupLocation = "\\NetworkLocation\Backups"
    )
    begin{

    }
    process{

        [string]$backupFile = "$BackupLocation\$SourceDatabase.bak"

        if([System.IO.File]::Exists($backupFile)){
            [System.IO.File]::Delete($backupFile);
        }

        #ensure source is pingable
        if(!(Test-Connection -ComputerName $SourceServer -Count 2 -Quiet)){
            throw "Source server $SourceServer could not be contacted.  Please verify that this server is correct or is reachable."
        }

        #ensure destination is pingable
        if(!(Test-Connection -ComputerName $DestServer -Count 2 -Quiet)){
            throw "Destination server $DestServer could not be contacted.  Please verify that this server is correct or is reachable."
        }

        #ensure db exist on source and is online and can be backed up
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$SourceServer,$Port"
        $db = $srv.Databases | ?{$_.Name -eq $SourceDatabase -and $_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal}
        if(!$db){
            throw "Database $SourceDatabase on server $SourceServer does not exist, or is not in a state that allows backups."
        }
        
        #backup source database
        $srv.ConnectionContext.StatementTimeout = 0;
        Backup-SqlDatabase -InputObject $srv -Database $SourceDatabase -BackupFile $backupFile -CompressionOption On 
        $srv.ConnectionContext.Disconnect();

        $srv = New-Object Microsoft.SqlServer.Management.smo.Server "$DestServer,$Port"
        $cluster = gwmi -class "MSCluster_Cluster" -namespace "root\mscluster" -computername $DestServer -Authentication PacketPrivacy -ErrorAction SilentlyContinue

        if($cluster -and $srv.IsHadrEnabled){
            $sqlNodes = New-Object System.Collections.Generic.List[object]
            $agGroups = New-Object System.Collections.Generic.List[object]

            #if multiple ag groups, restore to the ag group with the least number of databases
            foreach($ag in $srv.AvailabilityGroups){
                [void]$agGroups.Add([PSCustomObject]@{
                    Name = $ag.Name 
                    PrimaryName = $ag.PrimaryReplicaServerName
                    DatabaseCount = $ag.AvalabilityDatabases.Count 
                });
            }
            $agGroup = $agGroups | sort DatabaseCount | select -First 1

            $srv.ConnectionContext.Disconnect();
            
            #get nodes in cluster to restore the db onto
            Get-Cluster -Name $cluster.Name | Get-ClusterNode | %{
                $nodeName = $_.Name 
                [void]$sqlNodes.Add([PSCustomObject]@{
                    ServerName = $nodeName
                });
            }

            #test the sql connectivity on the nodes before trying any restores. If any fail, boot.
            #additionally, test to make sure that the database doesn't already exist on any of the nodes.
            $sqlNodes | %{
                $serverName = $_.ServerName 
                $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection "$serverName,$Port"
                try{
                    $srvConn.Connect();
                    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn
                    if($srv.Databases[$SourceDatabase]){
                        throw "Database $SourceDatabase already exists on server $serverName.  Restore will not continue."
                    }
                }
                catch{
                    throw "Could not connect to server $ServerName.  Database will not be restored.  Exception is:`r`n$($_ | fl -force | out-string)"
                }
                finally{
                    $srvConn.Disconnect();
                }
            }

            #restore with norecovery to all nodes
            $sqlNodes | %{
                $serverName = $_.ServerName 
                $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$serverName,$Port"
                $srv.ConnectionContext.StatementTimeout = 0;
                Restore-SqlDatabase -InputObject $srv -Database $SourceDatabase -BackupFile $backupFile -NoRecovery
                $srv.ConnectionContext.Disconnect();
            }

            #connect to primary of group and recover just that database
            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$($agGroup.PrimaryName),$Port"
            $db = $srv.Databases[$SourceDatabase]  #just another check...
            if(!$db){
                throw "Database $SourceDatabase on server $($agGroup.PrimaryName) does not exist.  If the database $SourceDatabase exists on servers $($sqlNodes -join ',') they will need to be dropped before another deploy can be attempted."
            }
            $srv.ConnectionContext.ExecuteNonQuery("RESTORE DATABASE [$SourceDatabase] WITH RECOVERY;");
            $srv.ConnectionContext.Disconnect();

            #add primary to AG
            Add-SqlAvailabilityDatabase -Database $SourceDatabase -Path "SQLSERVER:\SQL\$($agGroup.PrimaryName)`,$Port\DEFAULT\AvailabilityGroups\$($agGroup.Name)" 

            #add subsequent nodes to AG
            $sqlNodes | ?{$_.ServerName -ne $agGroup.PrimaryName} | %{
                $serverName = $_.ServerName 
                Add-SqlAvailabilityDatabase -Database $SourceDatabase -Path "SQLSERVER:\SQL\$ServerName`,$Port\DEFAULT\AvailabilityGroups\$($agGroup.Name)"  
            }


        }
        else{
            #just a single instance restore....
            $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$DestServer,$Port"
            $srv.ConnectionContext.StatementTimeout = 0;
            if($srv.Databases[$SourceDatabase]){
                throw "Database $SourceDatabase already exists on server $DestServer."
            }
            Restore-SqlDatabase -InputObject $srv -Database $SourceDatabase -BackupFile $backupFile
            $srv.ConnectionContext.Disconnect();
        }

    }
    end{

    }
}

Read an Extended Events File via Powershell

Admittedly, I don’t know that much about this, I just started fooling around with this tonight (via this post).  As stated in said post, this works for only under x86 and AFAIK, needs at least Sql Server 2014 (I couldn’t find the required dll’s under sql 2012…).     Edit 20170410:  The 64 bit dll’s are located in ‘C:\Program Files\Microsoft SQL Server\120\Shared’ for sql server 2014.  I’m assuming they’re in the 130 directory for Sql Server 2016.

$path = '\\ServerName\C$\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\Log\AuditCapture_0_131300599577540000.xel'

#Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Extensions\Application\Microsoft.SqlServer.XE.Core.dll'
#Add-Type -Path 'C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Extensions\Application\Microsoft.SqlServer.XEvent.Linq.dll'
 
Add-Type -Path 'C:\Program Files\Microsoft SQL Server\120\Shared\Microsoft.SqlServer.XE.Core.dll'
Add-Type -Path 'C:\Program Files\Microsoft SQL Server\120\Shared\Microsoft.SqlServer.XEvent.Linq.dll'

$events = New-Object Microsoft.SqlServer.XEvent.Linq.QueryableXEventData($path)
$sb = New-Object System.Text.StringBuilder

$events | select -First 10 | %{
    $event = $_
    [void]$sb.Append("$($event.Timestamp);;");

    for($i=0;$i-lt($event.Fields.Count-1);$i++){  
        [void]$sb.Append("$($event.Fields[$i].Value.ToString().Replace("`r`n", ''));;");
    }
    
    $event.Actions | %{
        $action = $_
        [void]$sb.Append("$($action.value.ToString().Replace("`r`n", ''));;");
    }
    [void]$sb.Append("ServerName;;");

    [void]$sb.AppendLine();
}
$sb.ToString();

Restore Database with Move Files

More for me than you.  Something I type up all the time and forget where the heck I put it.  Not tested, but should give you the general idea.

Import-Module SqlPS -DisableNameChecking

$destServers = @('Server1', 'Server2', 'Server3')
$srcPath = "\\sharepath\Backups"

try{
    
    $destServers | %{
        $serverName = $_ 

        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$serverName,1433"

        gci -Path $srcPath | ?{$_.Extension -eq '.bak'} | %{
            $dbName = $_.BaseName 
            $backupFile = $_.FullName

            $backupDevice = 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($backupDevice);
            $restore.NoRecovery = $true;
            $restore.ReplaceDatabase = $true
            $restore.Database = $dbName

            $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);
            $dbFiles = $restore.ReadFileList($srv);
            $moveFiles = @();
            foreach($dbFile in $dbFiles){
                $fileName = [System.IO.Path]::GetFileName($dbFile.PhysicalName);
                switch($dbFile.Type){
                    'D'{
                        $moveFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "E:\MSSQL\Data\$fileName")
                    }
                    'L'{
                        $moveFiles += New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "G:\MSSQL\TranLog\$fileName")
                    }
                }
            }

            $srv.ConnectionContext.StatementTimeout = 0;
            restore-sqldatabase -InputObject $srv -Database $dbName -BackupFile $backupFile -RelocateFile $moveFiles -ReplaceDatabase
        }
    }

}
catch{
    $_ | fl -Force
}

BCP via Powershell

Quick post on how to use BCP with powershell.  Adjust options to suit your needs.

Import-Module SqlPS -DisableNameChecking

$query = "SELECT * FROM TABLE"

try{

 $srcConn = New-Object System.Data.SqlClient.SqlConnection("PersistSecurityInfo = true;Server=Server1;Database=DBName;Integrated Security=True;Application Name=PowerShell_BCP");
 $destConn = New-Object System.Data.SqlClient.SqlConnection("PersistSecurityInfo = true;Server=Server2;Database=DBName;Integrated Security=True;Application Name=PowerShell_BCP");

 $srcCommand = New-Object System.Data.SqlClient.SqlCommand($query, $srcConn);
 $destBulk = New-Object System.Data.SqlClient.SqlBulkCopy($destConn);
 $destBulk.BulkCopyTimeout = 0;
 $destBulk.BatchSize = 1000
 $destBulk.DestinationTableName = 'DestTable'

 $srcConn.Open();
 $destConn.Open();

 $results = $srcCommand.ExecuteReader();
 $destBulk.WriteToServer($results);

 $srcConn.Close();
 $destConn.Close();

}
catch{
 $_ | fl -Force
}

31 Posts of using Sql Server with Powershell — Post 4: Server Object

For the 4th post, we’re going to get go into managing Sql Server itself rather than the data.  For that, we’re going to need a new object, the Server object.  In this example, we’re using the ServerConnection object to connect to the Server object.  We then go through all the databases on the server and print out the name of the databases:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $Server = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

    foreach($database in $Server.Databases){
        $database.Name
    }

    $srvConn.Disconnect();

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

Pop-Location

Lets step it up a bit and work on more than one server at a time.  Create a text file named Servers.txt and put it on the root of your c:\ drive.  In said file, put the name of a few servers that you want to query:

image

If you don’t have more than one server, just go into your ConfigurationManager in windows and add a few alias’ in that point to your local instance (that’s what I did…).  Make sure to add them to both the 32-bit and 64-bit Sql Native Client alias’.

image

So, let’s iterate through all databases on all our servers:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    get-content -Path C:\Servers.txt | %{
        $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $_
        $Server = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

        Write-Host $_ -ForegroundColor Red

        foreach($database in $Server.Databases | where{!$_.IsSystemObject}){
            "Database name is:  $($database.Name)";
        }

        $srvConn.Disconnect();
    }

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

Pop-Location

The ‘%’ is an alias for ‘foreach-object’ for the list of servers in the text file.  The ‘$_’ is just a reference to the current item in the list of servers in the text file.

  • Use Get-Content to retrieve the contents of our c:\Servers.txt file.
  • Iterate through the servers in the file, each time changing the server name ($_) in the ServerConnection object.
  • Filter using the where-object to only show non-system databases (where{!$_.IsSystemObject})

The $database.name is surrounded by $() so it can evaluate the database name.  If it weren’t there, the string would print out the database name with .Name after it, as shown below:

image

Even though I’m using a ServerConnection object to connect via the Server object, you don’t have to do this.  You can just as easily pass the servername string into the Server object and it will work just fine.

$Server = new-object Microsoft.SqlServer.Management.smo.Server 'bacon'

So, the server object is the object you’ll most likely be using the most when administering the server.  Under the server object, you’ll find logins, databases, server roles, the sql agent job server, etc…

In upcoming posts, we’ll go through examples of adminstering the most common objects  that hang off the Server object.

31 Posts of using Sql Server with Powershell — Post 3: Retrieving Data

For the 3rd post, we’re going to look at retrieving data from Sql Server.  It’s not much more complicated than the second post, but it does involve some new objects.

On the last post, we finished off with inserting some data into the database using a ServerConnection object.  Now, we’re going to execute a query to get that data back out.  When you call the ExecuteReader method of the ServerConnection object and pass it in a SELECT statement, it will return you a SqlDataReader object:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.Connect();

    $sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
    while($sdr.Read()){
        $sdr.GetInt32(0)
        $sdr.GetString(1)
    }
}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

This does a while loop on the SqlDataReaders’ Read() method, which will return $true as long as there are records to be read.

image

While using the SqlDataReader is fast and efficient, it’s not the most user-friendly object to use for reading data in powershell.  For ease of use, you can’t beat a DataTable.

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $dataTable = New-Object System.Data.DataTable
    $srvConn.Connect();

    $sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
    $dataTable.Load($sdr);
    $dataTable | ft -AutoSize

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

Pop-Location

In this code, we’re still executing the same statement and loading the SqlDataReader object, but then we create a new System.Data.Datatable object and call the Load method on the DataTable and pass in the SqlDataReader.  This object is probably the easiest object to work with for reading data from Sql Server with powershell.  Here’s another example using a DataTable to export data to a csv file:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $dataTable = New-Object System.Data.DataTable
    $srvConn.Connect();

    $sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
    $dataTable.Load($sdr);
    $dataTable | Export-Csv -Path "$env:windir\temp\TestCSV1.csv" -Force -NoTypeInformation

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

Pop-Location

The –Force will overwrite the file if it already exists.  The –NoTypeInformation does what it says, it omits type information from the file.  If you don’t specify this, you’ll see extra type information output into the csv file that you probably don’t want.

You can also filter on the values in the DataTable via the where-object clause in powershell as well:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $dataTable = New-Object System.Data.DataTable
    $srvConn.Connect();

    $sdr = $srvConn.ExecuteReader("SELECT TOP 5 * FROM dbo.Test")
    $dataTable.Load($sdr);
    $dataTable | where{$_.ID -gt 3} | %{
        $_
    }

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

Pop-Location