How can I extract the value of a varbinary(max) column?

Question

I have a varbinary(max) column that is storing images in an SQL database.

I am working on a newdb script, where an application creates a new instance of the db and populates a few of the tables. One of those tables I am working on is initializing that image column.

In order to do this, I printed the contents of the column using a select statement and pasted the content into the insert statement of the newdb script. This appeared to work initially, but the image didn't load correctly.

So I compared the DATALENTH() of the original data (5469988) and the new data (21839). It appears the Microsoft SQL Server management Studio - 2014 cut off the data why I copied it from the original db at a certain point. I need to be able to get the entire content of the column. Any ideas?

Answer 1

select cast(convert(varchar(max), VarBinaryMaxColumn, 1) as xml) from Table