How to copy blob (binary data) from one DB to DB using a script?

Question

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.

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]
GO
/****** Object:  Table [dbo].[tjc_blob]    Script Date: 07/04/2017 14:46:23 ******/
INSERT [dbo].[tjc_blob] ([id], [datablob])
VALUES (1, 0x6162636465666768696A6B6C6D6E6F707172737475767778797A)