Ad-hoc retreival of data from SQL Server varbinary column

Question

I would like to retreive some binary data from a varbinary(max) column in a SQL Server database for debugging purposes.

What is the easiest way to get this data into a local binary file, preferably without having to write a throw-away console application?

I have tried using SQL Server Management Studio (with the "results to file" option) but this outputs a hex encoded binary string to the file, rather than the raw binary data.

Answer 1

I can't think of any easier way to do this than a throw away bit of C#...

    static void Main(string[] args)
    {
        GetBinaryDataToFile("Server=localhost;Initial Catalog=ReportServer;Integrated Security=true", "D:\\temp.dat");
    }

    public static void GetBinaryDataToFile(string connectionString, string path)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = connection.CreateCommand())
            {
                command.CommandText = "SELECT Sid FROM Users WHERE UserID = '62066184-8403-494E-A571-438ABF938A4F'";
                command.CommandType = CommandType.Text;

                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        SqlBinary sqlBinary = dataReader.GetSqlBinary(0);
                        File.WriteAllBytes(path, sqlBinary.Value);
                    }

                    dataReader.Close();
                }
            }

            connection.Close();
        }
    }

This code has been tested using the Users.Sid column (which is of varbinary type) in a default installation of SQL Server wih Reporting Services.