LLI Wiki — My Digital Knowledge Base

PowerShell : exporter un blob SQL Server vers des fichiers

Script PowerShell qui exporte des colonnes binaires SQL Server vers des fichiers, par morceaux avec GetBytes et un FileStream.

Ce script PowerShell exporte des colonnes binaires (varbinary(max), image) de SQL Server vers des fichiers, sans charger chaque blob entièrement en mémoire. La première colonne de la requête contient le nom du fichier, la seconde le blob. Chaque blob est lu par morceaux avec SqlDataReader.GetBytes() puis écrit dans un FileStream.

Script

## Export of "larger" Sql Server Blob to file
## with GetBytes-Stream.
# Configuration data
$Server = ".\SQLINSTANCE";        # SQL Server instance.
$Database = "AdventureWorks2008";
$Dest = "D:\Export\";             # Path to export to.
$bufferSize = 8192;               # Stream buffer size in bytes.

# Select-Statement for name & blob
# with filter.
$Sql = "SELECT [FileName]
              ,[Document]
        FROM Production.Document
        WHERE FileExtension = '.doc'";

# Open ADO.NET Connection
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$Server;" +
                        "Integrated Security=True;" +
                        "Initial Catalog=$Database";
$con.Open();

# New Command and Reader
$cmd = New-Object Data.SqlClient.SqlCommand $Sql, $con;
$rd = $cmd.ExecuteReader();

# Create a byte array for the stream.
$out = [array]::CreateInstance('Byte', $bufferSize)

# Looping through records
While ($rd.Read())
{
    Write-Output ("Exporting: {0}" -f $rd.GetString(0));
    # New BinaryWriter
    $fs = New-Object System.IO.FileStream ($Dest + $rd.GetString(0)), Create, Write;
    $bw = New-Object System.IO.BinaryWriter $fs;

    $start = 0;
    # Read first byte stream
    $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);
    While ($received -gt 0)
    {
       $bw.Write($out, 0, $received);
       $bw.Flush();
       $start += $received;
       # Read next byte stream
       $received = $rd.GetBytes(1, $start, $out, 0, $bufferSize - 1);
    }

    $bw.Close();
    $fs.Close();
}

# Closing & Disposing all objects
$fs.Dispose();
$rd.Close();
$cmd.Dispose();
$con.Close();

Write-Output ("Finished");

Remarques

Mastodon LLIMastodon SocialFosstodon