Exclude column data from one table when restoring from a BACPAC file

Question

I have a .bacpac file from a large database.

The problem is it contains one table which has files stored as varbinary and is extremely large. This table is to large to restore locally.

I would like to restore the .bacpac file, but instruct it to ignore that varbinary column.

If this were RAW BCP, I know I could construct a format file for this table which simply skips the column (leaving it null, which would be fine). However, I cannot figure out how or where to apply a BCP command in the structure of the .bacpac file that would do that?

Is there a way to provide instruction to a .bacpac restore to ignore a single table column (like I could in BCP)? Or perhaps a way to edit the .bacpac file (zip) contents to insert an instruction for the BCP operation on this single table?

Answer 1

I'm not aware of a direct option to selectively aware ignore specific columns during a bacpac restore. 2 options I can think of:

Option 1

• restore the db to a temporary db in azure or sql server

• use a script to create a new db without the varbinary column and copy the data from the temporary db to the new one.

  -- Create a new database
  CREATE DATABASE NewDatabase;
  GO
  
  -- Switch to the new database
  USE NewDatabase;
  GO
  
  -- Create the schema and table without the varbinary column
  CREATE SCHEMA YourSchema;
  GO
  
  CREATE TABLE YourSchema.YourTable (
      Column1 DataType1,
      Column2 DataType2,
      --...
      -- Exclude the varbinary column
      --...
      ColumnN DataTypeN
  );
  GO
  
  -- Insert data from the temporary database to the new one (excluding the varbinary column)
  INSERT INTO YourSchema.YourTable (
      Column1, Column2, /*...,*/ ColumnN
  )
  SELECT
      Column1, Column2, /*...,*/ ColumnN
  FROM
      TempDatabase.YourSchema.YourTable;
  GO
  

Option 2

• If it's too large to restore at all with the varbinary I'd try to create a copy of the original db without the varbinary and export the bacpac file