Create Image files from MS SQL Image Data Type with Powershell

Today my brother Patrick asked me to pull pictures from our ID badge System to be used for Active Directory. I found that the images where stored in a Microsoft SQL 2000 instance as the image data type. After some work with Powershell I was able to to recreate the Image file from the SQL data. This example is of course a simplifed version but should be easy enough to modify for your needs.

$picture_ID = 1
$file = "c:\image.jpeg"
$sqlserver = "DBServer"
$SQLCommand = "Select image 
  From ImageTable
  Where ID = '$picture_ID'"
#note image is an SQL Type "Image"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$sqlserver;Database=master;Integrated Security=True"
$SqlConnection.Open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SQLCommand
$SqlCmd.Connection = $SqlConnection
$dbname = $SqlCmd.ExecuteScalar()
$SqlConnection.Close()

Set-Content -path $file -Value $dbname -Encoding Byte 
start $file #open the file to view if its correct.

In my case I didn't know the files were jpeg but used http://www.sqlimageviewer.com/ to first see what type of tiles they are. Then change the file extension. Also the trial was sufficient for this, no need to buy it.

Additionally I modifyed a fuction from Jason Fossen to demo the return array when it was returned as a string to create a byte array and

function Convert-HexStringToByteArray {
Param ( [String] $String )

#Clean out whitespaces and any other non-hex crud.
$String = $String.ToLower() -replace '[^a-f0-9\\\,x\-\:]',''

#Try to put into canonical colon-delimited format.
$String = $String -replace '0x|\\x|\-|,',':'

#Remove beginning and ending colons, and other detritus.
$String = $String -replace '^:+|:+$|x|\\',''

#Maybe there's nothing left over to convert...
if ($String.Length -eq 0) { ,@() ; return } 

#Split string with or without colon delimiters.
$String -split '([a-f0-9]{2})' | foreach-object { 
 if ($_) {
   [System.Convert]::ToByte($_,16)  
 }
} 
}

$file = "c:\image.jpeg"
$img = "0xFFD8FFE000104A46......" #excluded the rest for size reasons
$ba = Convert-HexStringToByteArray $img
Set-Content -path $file -Value $ba -Encoding Byte 
start $file #open the file to view if its correct.