Share via

How To: Export SQL Server FileStream Data with PowerShell

  • Article

In opposition to simple data like numbers and text, it's not possible to verify blob data for validity and correctness just by screening the binary dump.
Only when the respective application is accessing data it is may pointing to be bad data. In order to examine the data for accuracy, the DBA has to export them to open it with the appropriate program.

Blob data can be exported using PowerShell in a simple way, by querying the data with Ado.Net - SqlClient + SqlFileStream to write it on local hard drive:

## Export of Sql Server FileStream (Blob) to file            
## with SqlFileStream            
            
# Configuration data            
$Server = "(local)";              # SQL Server Instance.            
$Database = "AdventureWorks";            
$Dest = "D:\Export\";             # Path to export to.            
$bufferSize = 8192;               # Stream buffer size in bytes.            
            
# Open ADO.NET Connection            
$con = New-Object Data.SqlClient.SqlConnection;            
$con.ConnectionString = "Data Source=$Server;" +             
                        "Integrated Security=True;" +            
                        "Initial Catalog=$Database";            
$con.Open();            
            
# Start new transaction and get FileStream transaction context            
[System.Data.SqlClient.SqlTransaction]$tran = $con.BeginTransaction("fs");            
$Sql = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";            
$ctx = [array]::CreateInstance('Byte', 16);            
$cmdct = New-Object Data.SqlClient.SqlCommand($Sql, $con, $tran);            
$ctx = $cmdct.ExecuteScalar();            
$cmdct.Dispose();            
            
# Select-Statement for file name & FileStream-PathName            
# with additional filter.            
# !My example table! Do not exists in AdventureWorks by default!            
$Sql = "SELECT [FileName] 
              ,[FileStreamData].PathName()
        FROM dbo.FileStreamStorage ";            
            
# Create a byte array for the stream.            
$out = [array]::CreateInstance('Byte', $bufferSize);            
            
# New Command and Reader            
$cmd = New-Object Data.SqlClient.SqlCommand($Sql, $con, $tran);            
$rd = $cmd.ExecuteReader();            
# Looping through records            
While ($rd.Read())            
{            
    Write-Output ("Exporting: {0}" -f $rd.GetString(0));                    
    # New SqlFileStream            
    $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write;            
    $sfs = New-Object System.Data.SqlTypes.SqlFileStream $rd.GetString(1), $ctx, Read, None, 0;            
                
    $sfs.CopyTo($fs)

            
    $fs.Close();            
    $sfs.Close();            
}            
            
# Closing & Disposing all objects            
$fs.Dispose();            
$sfs.Dispose();            
$rd.Close();            
$rd.Dispose();            
$tran.Commit();            
$cmd.Dispose();            
$tran.Dispose();            
$con.Close();            
$con.Dispose();            
            
Write-Output ("Finished");

 

References

See Also