Extracting Data from SQL Database BLOB Fields: The BLOB File Extractor

3 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 0 LinkedIn 3 3 Flares ×

I started my second Codeplex project recently, and did the first release of it last week (I think). Its another really simple utility, that I hope is useful (please let me know if it is, or if you have any feature requests). So what is it?

Over the years working with Dynamics CRM and various CMS systems including SharePoint, I have found occasions when its useful to extract a BLOB file directly from the database to disk. This could be for a number of reasons – the site is down, its actually from a database backup, or for n number of random occurences. Whenever I’ve encountered this I’ve always just span up a bit of code to get the field and write it to file. So all I’ve done is wrap up this code into a simple utility that can be used by anybody – without the need to compile code: SQL BLOB File Extractor: http://sqlblobextractor.codeplex.com/. Its on codeplex, its free and you can have the source code!

Its a bit bare bones at the minute – but I’ll try to get the project updated with some usage instructions soon!

Matt

, ,

  • Joe

    How do you tell the BLOB File Extractor to export multiple files and what to name them? Everything I try to put in Enter File Name works and creates a single file. I’m looking for something on how to set a column as the name of the file so I can return and identify multiple files at once.

    • Hi Joe,

      I’m afraid the tool doesn’t currently allow that – I’ll look at amending it and getting a new version out with that feature.

      Matt

    • Hi Joe (again)

      I’ve just added a new release of the SQL Blob File Extractor that now allows you to specify a query column as the file name – hope this helps.

      Note that you’ll need to uninstall the old version first, as I’ve had to move the installer to a Wix installer (Microsoft discontinued the existing setup project).

      Matt

  • saad101

    Its useful but only for a single file extract. My requirement is to download all the records and name each one same as another coulmn name. I tried writing it in vb, but it seems to break the after the first iteration of the loop.
    My objective is to extract all the attachments form the column in the table. This is for a migration project. Please help 🙁

    • It can actually extract multiple files now:

      1. In the SQL field you select multiple rows
      2. Then select the correct column name for the blob data
      3. Enter the folder you want the results in
      4. Select the “Choose column as filename” radio button and select the column name you want to use as your file name.
      5. Enter the file extension (i.e. pdf or jpg etc)
      6. Press next and it should write every row out to a file.

      Let me know if you’re having trouble

  • Josh

    Matt, this utility is awesome!!!! In playing around with it for a couple minutes it does EXACTLY what I was looking for in pulling the physical files stored in BLOB format from a Dynamics database onto my local machine.

    I have noticed that anytime I try to pull more than a couple hundred records out this way I get an error “Exception of Type ‘system.outofmemoryexception’ was thrown”. In researching this error it appears most people get this when they run a massive query in SQL Server Management Studio to a grid.

    Do you know why this error would be getting thrown or a workaround to bypass it to give the system more time?

    • Hey Josh, its generally going to be something to do with the size of the data its trying to process. I’ll have a look at the tool to see if there’s anything I can do to alleviate the issue.

      Is the problem occurring when you’re retrieving a high number of records, or a small number of very large records?

      • Josh

        That did it Matt! Before it looked to be processing the query when you clicked Next, now it looks like it running the process after the parameters are set on the second screen. I monitored my RAM and before it was hitting at over 1 GB when I submitted the query and now it doesn’t hit at more than 50 MB at any time.

        The first couple times I tried pulling attachments out I would only be able to query about 100-500 at a time, with this update I ran an export that pulled 5000 out and it easily could have done more. Very cool Matt!

        • Great, glad it worked!

          What you’ve said is exactly right – before I pulled everything back in a batch and processed them from memory – I changed it so that it only processes one in memory at a time. When I originally created the tool I never really though of doing hundreds or thousands at a time!!!

    • I just uploaded a new version of the tool (1.0.3) that should better handle large datasets. Give it a try and let me know how you get on.

  • Andrew Moore

    Hey Matt,
    I’m not sure how the sql part works or what its looking for.
    I need to extract blob files then name them from a different column.
    Example:
    The blob file column is photo and the file name column is IDNumber
    Also is this scriptable? meaning can I pass info/arguments/commands through the command line to it?

    • Hey Andrew

      In your case the sql just needs to return the blob file column (photo), the name column (IDNumber) for therows you want to extract. (e.g. SELECT Photo, IDNumber FROM etc). Then the next screen should let you identify which column is the blob file column, and which on to use as the name.

      Unfortunately there isn’t a command line interface at the moment, but its definitely something that could be added.

      Matt

      • Andrew Moore

        Hey Matt,
        Thanks for the reply, I was wondering if there was a second screen after putting the sql in. I ended up creating, compiling, getting help from stack overflow a vbscript that does everything I need an allows me to script it the exporting

  • Dallas

    Hey Matt,

    I’m trying to extract a single blob file, and no matter what I do, whenever I press “yes” on the “Confirm Write?” prompt, I get “Please ensure all data is fully completed”. I can connect to my SQL Server just fine, my query checks out (I get the correct Data Column Name), but I just can’t seem to get an extract to work.. I have all information fields filled in, including destination folder, file extension, ect. Any idea as to what might be causing my extract to fail?

    Thanks,

    Dallas

  • ira sha

    I am trying to use this app. the connection string work fine, what does it go in SQL window?

    • The SQL window is the SQL query that you use to retrieve your data from the database

      • Mahmudul Hasan Sarker

        Hi Matt,
        would you please help with the query writing? I’ve lots of file to extract into PDF files in a folder. I will e very happy if you can help me in this.

        Thanks
        Hasan

        • Hi Hasan – I’d be happy to lend a hand but I’ll need more information about the database / table structure. Drop me an email at matthew.whetton@gmail.com and I’ll see what I can do.

  • Jack

    Great tool! Thank you for the work you’ve put into this. Is it possible to specify the filetype (if mimetype already stored) when it downloads? I have a table that contains pdfs, doc, docs, rtf, etc. and have the mimetype data stored in the table as well. I’d love to be able to select the mimetype field and have it associate it with the field when downloading. Thanks again!

    • Hey Jack,

      I’ve just added this feature and uploaded a new beta on codeplex (link below).

      https://sqlblobextractor.codeplex.com/releases/view/616486

      I’ve done some basic testing but it’d be good if you could you let me know how you get on before I make it the stable release.

      Cheers

      Matt

      • Jack

        Hi,

        Thanks again. I’ve used v1.0.4 of the extractor to specify MIME Type as the field. However, I’ve not been able to correctly extract the files. The files extract perfectly, it is just the extension that is not being associated with it. In the database I am attempting to use it on. The filetype only comes back as ‘File’

        Initially, I had just used the Extractor’s SQL query to specify the MIME Type (WHERE MIMEType = ‘application/pdf’) and explicitly said ‘pdf’ as the format and it works perfectly. I inherited the database, so it may NOT be the best test case (as I’m unfamiliar with the ins-and-out). The mimetypes that are in the field for me are ‘application/msword’ and ‘application/pdf’

        Thanks for your hard work on this. It saved me hours of development time and research!

        • Hi Jack,

          My apologies, there was a bug in the mime type translation. I’ve updated to 1.0.5 which should sort the issue: https://sqlblobextractor.codeplex.com/releases/view/616504

          Glad that its been of help – its only a small project I maintain but it means a great deal to hear that its helping people!

          As before, let me know how you get on!

          Matt

          • Jack

            Works great now. Thanks again

          • Glad to hear it! Thanks for the feedback.

            If you have 5 minutes to spare could you leave a review for release 1.0.5 on CodePlex. They just help keep the project alive and let people know its worth giving it a try!

            Cheers

            Matt

  • Guillermo Perez-Santalla

    I had a my own blog. When I closed down the blog the company sent me a back up file for my content in SQL format. I want to extract the text from my blog form this file. Is there anyone that can provide this service for me?

  • Babloo

    Hello Matt – This is a great utility – I have a question though, is there a way to run this utility under task manager like can we create a batch script that can call this utility and use my last settings or maybe we can store the settings somewhere – I want this script to run everyday at midnight to export new data to my preferred location.

    • Hi Babloo

      I’m afraid this isn’t possible at the minute, but I’ll make a note and look at adding it as a feature. Unfortunately I’m a little snowed under at the minute so may take a little while for me to get it done.

      Matt

  • abcxyz1

    Brilliant utility; exactly what I was looking for.

    Works great, thanks!

  • Dave

    Hello all,

    I know an old thread but I am using this tool, and it does work but seems to have issue with any files that are over 125KB, it extracts them but even if a file I 1MB it extracts it as 125KB and the file is obviouly corrupt, did anyone else have this issue / overcome it?

    Thanks

    • Hi Dave, I’ll take a look and see if I can spot where the problem is

      • Dave

        Hi, I am starting to wonder is this a different issue as I have used powershell to extract a single file that should be 4mb and that has also come down at only 125kb.

        Dave

        • Hi Dave,

          I just tried a test and it looks like I can extract files of sizes well above 125kb.

          Could that be a database column max size?

          • Dave

            Hi sorry to have wasted your time, the column is a varbinary(Max) I am back looking at the source data at the moment see if something is wrong.

            Again, sorry I caused you more work, and thanks again for such a great tool!

            Dave

          • No problem – let me know if you need any help or find anything wrong with the extractor

  • Chad Sugden

    Hello Everyone:

    I just found this and I realize it is an older project but if anyone can answer a couple questions it would be great. I am able to browse through the connection wizard and find my SQL server and the database I want successfully but I can’t seem to get any further. When I press the next button I get an error please ensure that the connection string and SQL are properly defined but once I close that error the screen for configuring the options closes.

    Any suggestions or an explanation of the t-sql format to manually write the query?

    Thank you,

    Chad

    • Hi Chad,

      After you’ve completed the connection string dialog you need to enter a sql statement in the sql box below. The sql statement just needs to be a SELECT statement that gets the blob data you want to extract.

      Regards

      Matt

  • Chad Sugden

    Matt:

    Awesome that works I see that it just passes through the sql statment so all my joins etc. work. Off hand is there a way to use another column to set a sub directory? (i.e. set the base output directory and use a value from the query to break up the files into sub folders)

    Thanks for a great utility it really is helping me out with a project and as I am not a programmer it has saved me a ton of work.

  • Cathy Fellows McQuitty

    It looks like I’m the only one having problems extracting the pictures in color. I just get a black filled extract of the person’s body. Any suggestions?

    • Sounds really unusual – seems more likely that its something to do with the actual stored data than the extraction. The extraction just reads out the data and writes it to disk.

      • Cathy Fellows McQuitty

        I figured it out. The query I had built was pulling the wrong blob type, for every picture there were two types: 0 and 1. I’m pulling zeros and the pictures that are exported have color. Thank you for such a quick reply!!

  • Alessio Passalacqua

    Matthew, when i try to use Oracle Driver, the program crash.
    It seems it has problem with .net framework [2940] not catched on the BlobFileExtractor.exe what can i do for it?

    • Hi Alessio,

      I’m afraid I dont have an Oracle environment to test this on. Does you have any more information on the exception?

      Matt

The Essential App Marketing Kit
Subscribe To My Newsletter To Get an Entire Chapter From The Book for FREE
Never display this again
3 Flares Twitter 0 Facebook 0 StumbleUpon 0 Google+ 0 LinkedIn 3 3 Flares ×