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

qrjkbowd  于 2023-04-04  发布在  其他
关注(0)|答案(1)|浏览(131)

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?

r6l8ljro

r6l8ljro1#

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

相关问题