Script to save varbinary data to disk

Question

I have some varbinary data stored in a table in MS Sql Server 2005. Does anyone have SQL code that takes a query as input (lets say the query guarantees that a single column of varbinary is returned) and outputs the bytes to disk (one file per row?) I'm sure this has been asked a thousand times before, but Googling comes up with mostly .net solutions. I want an SQL solution.

Answer 1

The BCP approach does not work for me. The bytes it writes to disk cannot be deserialized back to the .net objects I stored. This means that the bytes on disk aren't equivalent to what's stored. Perhaps BCP is writing some kind of header. I'm not sure.

I found the following code here at the bottom of the article. It works great! Although it was intended for stored BMP images, it works with any varbinary.

DECLARE @SQLIMG VARCHAR(MAX),
    @IMG_PATH VARBINARY(MAX),
    @TIMESTAMP VARCHAR(MAX),
    @ObjectToken INT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR 
        SELECT csl_CompanyLogo from mlm_CSCompanySettingsLocalizations

OPEN IMGPATH 

FETCH NEXT FROM IMGPATH INTO @IMG_PATH 

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @TIMESTAMP = 'd:\' + replace(replace(replace(replace(convert(varchar,getdate(),121),'-',''),':',''),'.',''),' ','') + '.bmp'

        PRINT @TIMESTAMP
        PRINT @SQLIMG

        EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
        EXEC sp_OASetProperty @ObjectToken, 'Type', 1
        EXEC sp_OAMethod @ObjectToken, 'Open'
        EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
        EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @TIMESTAMP, 2
        EXEC sp_OAMethod @ObjectToken, 'Close'
        EXEC sp_OADestroy @ObjectToken

        FETCH NEXT FROM IMGPATH INTO @IMG_PATH 
    END 

CLOSE IMGPATH
DEALLOCATE IMGPATH

Answer 2

I am adding this to build on JohnOpincar's answer, so that others who want to use LinqPad can get a working solution faster.

/*
This LinqPad script saves data stored in a VARBINARY field to the specified folder.
1. Connect to SQL server and select the correct database in the connection dropdown (top right)
2. Change the Language to C# Program
3. Change "Attachments" to the name of your table that holds the VARBINARY data
4. Change "AttachmentBuffer" to the name of the field that holds the data
5. Change "Id" to the unique identifier field name
6. Change "1090" to the identity of the record you want to save
7. Change the path to where you want to save the file. Make sure you choose the right extension.

Notes: Windows 10 may give you "Access Denied" error when trying to save directly to C:\. Rather save to a subfolder.
*/

void Main()
{
    var context = this;
    var query = 
        from ci in context.Attachments
        where ci.Id == 1090
        select ci.AttachmentBuffer
    ;
    byte[] result = query.Single().ToArray();
    File.WriteAllBytes(@"c:\DEV\dumpfile.xlsx", result);
    Console.WriteLine("Done");
}

Answer 3

You can use BCP, not T-SQL, but works well.

BCP "SELECT FileContent FROM table WHERE ID = 1" queryout "C:\file.txt" -T

Answer 4

If you have linqpad, this works:

void Main()
{
    var context = this;
    var query = 
        from ci in context.Images
        where ci.ImageId == 10
        select ci.Image
    ;
    var result = query.Single ();
    var bytes = Convert.FromBase64String(result);
    File.WriteAllBytes(@"c:\image.bmp", bytes);
}

Answer 5

I know it's an old post, but I figured out why the following doesn't work and how to fix it:

BCP "SELECT FileContent FROM table WHERE ID = 1" queryout "C:\file.JPG" -T -N

The reason is bcp put Prefix Length at the very beginning of the file. It is either 4 bytes or 8 bytes, depends on data type of FileContent column (text, ntext, image: 4 varchar(max), varbinary(max) : 8 Refer to https://msdn.microsoft.com/en-us/library/ms190779.aspx)

Use a binary editor, like the one in Visual Studio, to remove the prefix bytes, and everything runs perfectly. :-)

Answer 6

With Powershell

function SQLExecuteScalar([string]$pServer, [string]$pDatabase, [string]$pQuery)
{
    # Connection
    $pSQLConnection = New-Object System.Data.SqlClient.SqlConnection
    $pSQLConnection.ConnectionString = "Data Source=$($pServer);Initial Catalog=$($pDatabase);Integrated Security=SSPI;Application Name=FileExtractor.Powershell"
    $pSQLConnection.Open()

    # Command
    [System.Data.SqlClient.SqlCommand]$cmd = New-Object System.Data.SqlClient.SqlCommand($pQuery, $pSQLConnection)

    # Execute and Get scalar value
    [byte[]]$return = $cmd.ExecuteScalar()
    
    # Close Connection
    $pSQLConnection.Close()

    # Result to pipe
    return $return
}

[string]$Server = "MyServer"
[string]$DataBase = "MyDb"
[string]$Query = "select BlobValue from dbo.MyTable"
[string]$FileName = "C:\Temp\BlobValue.bin"

SQLExecuteScalar -pServer $Server -pDatabase $DataBase -pQuery $Query | Set-Content $FileName -Encoding Byte

Answer 7

Just an alternative. You can use the freeware Toad for SQL server and save directly from the editor.

You can go to their website https://www.toadworld.com and get the freeware there or a 30 day trial of the full version. Its under Download and pick Toad for SQL server.

You do a regular select statement in Toad on the line that has the image you want to save. When you see the results you can click on the byte image column and on the right you see a PDF tab if this is a PDF document or on the left you see a Image tab. When you click the tab you can see the save logo at the bottom to save the image or file.

Answer 8

You can easy do that in Powershell:

# Define your T-SQL query
$query = "SELECT [YourVarbinaryColumn] FROM [YourTable] WHERE [YourCondition]"

# Define your database connection parameters
$serverName = "YourServerName"
$databaseName = "YourDatabaseName"
$username = "YourUsername"
$password = "YourPassword"

# Create a new SqlConnection object
$connectionString = "Server=$serverName;Database=$databaseName;User ID=$username;Password=$password;"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

# Open the database connection
$connection.Open()

# Create a new SqlCommand object
$command = $connection.CreateCommand()
$command.CommandText = $query

# Execute the T-SQL query and retrieve the results as a DataTable
$dataTable = New-Object System.Data.DataTable
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
$dataAdapter.Fill($dataTable)

# Close the database connection
$connection.Close()

# Extract the binary data from the first row of the DataTable
$binaryData = $dataTable.Rows[0]["YourVarbinaryColumn"]

# Convert the binary data to a string
$htmlString = [System.Text.Encoding]::UTF8.GetString($binaryData)

# Define the path and filename for the HTML file
$filePath = "C:\YourDirectory\YourFilename.html"

# Create the HTML file and write the HTML string to it
Set-Content -Path $filePath -Value $htmlString -Encoding UTF8

Answer 9

SQL is designed to work with database objects, so from it's point of view anything else doesn't exists. Sure, there are extended procedures like xp_cmdshell that allow you interact with the operating system, but they are proprietary extensions and not part of T-SQL.

Maybe the closest approach would be using the FILESTREAM attribute for binary types of SQL Server 2008, which allow storing some columns directly as files in a folder instead of using the database:

FILESTREAM overview

Note that the FILESTREAM storage is designed for maintain large files out of the database in order to increase performance, and not for allowing direct access to files (i.e. T-SQL still doesn't have the concept of a filesystem). I my opinion, direct access to the filesystem from SQL will defeat some of the purposes of a database (mainly having data stored in a structured way).

So I would recommend following the advice of Dustin and use a tool like BCP or any other data dumper.