We are trying to copy a blob (binary data) from one Database to another Database using a simple script. This is the steps we do.

  1. Select the value using MSSQL Studio Client.
  2. Copy it into the clipboard.
  3. Paste it into anINSERT script using notepad.
  4. Apply it into the destination database.

We notice that the value is now an ASCII that starts with 0x255.... which seems to be the hexadecimal representation of the binary data. Is there a way we can convert it back to binary during the insert?

Is there a better way to copy blob data using scripts? Assume, we don't have direct connection to destination Database.

1 Answer 1


I would use the "Generate Scripts" option in SQL Server Management Studio.

  1. In Object Explorer - highlight the database hosting the source table.
  2. Right-click menu - Tasks | Generate Scripts
  3. Select specific table object - click Next page
  4. Click Advanced button
  5. Under General Options - Change 'Type of data to script' to "Data Only"
  6. Save to file - change folder and filename as required.

I tried a simple test with a table storing a varbinary field. The output from the Scripting process looked ok:

USE [TestDB]
/****** Object:  Table [dbo].[tjc_blob]    Script Date: 07/04/2017 14:46:23 ******/
INSERT [dbo].[tjc_blob] ([id], [datablob])
VALUES (1, 0x6162636465666768696A6B6C6D6E6F707172737475767778797A)
  • You, sir, are my savior. Thank you. Nov 11, 2019 at 9:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.