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();
}
Stop/Disable SqlAgent/MSSQLServer service via Powershell
One-liner to stop and disable the sqlagent and msssqlserver service.
Get-Service MSSQLSERVER, SQLSERVERAGENT | Stop-Service -PassThru -Force | Set-Service -StartupType disabled
Remove ReadOnly Routing from a Replica
ALTER AVAILABILITY GROUP [AvailabilityGroupName] MODIFY REPLICA ON ’ServerName′ WITH( PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=NONE) );
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{
}
}
Capture Sql Messages output to Variable
To capture the sql output messages (what you see in the “Messages” tab in SSMS) you can use this:
$results = (Invoke-Sqlcmd -ServerInstance "ServerName" -Database master -Query "BACKUP DATABASE [master] TO DISK = N'c:\master.bak' WITH Stats = 5" -Verbose | Out-Null) 4>&1 $results
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:
Right then, let’s call it from an agent job then via cmd line:
Looks good. Let’s run it!
Uhhh….mkay….let’s check the history of said ‘successful’ job:
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:
Remove that, and viola:
Job now shows as failed:
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
}
