What are the .BCP files inside a .bacpak file?

All we need is an easy explanation of the problem, so here it is.

Diving on dba.stackexchange.com I found a nice answer that teach me how to open a .bacpac file with File Explorer.

I gave it a try with the database AdventureWorks2008R2 and all I did was:

  1. Save the database as .bacpac file
  2. Rename the file extension to .zip
  3. Unzip it

And there you go, you can see the tables of the database as folders and inside each folder you can see the raw data.

What are the .BCP files inside a .bacpak file?

But when I open the folder Person.Address I see there are 38 files into it with extension .BCP.

What are the .BCP files inside a .bacpak file?

At the beginning I taught they were the pages in which the data are split. But those cannot be pages because they weight about 67KB. Pages should weight 8KB.

So I checked the number of pages with this query:

-- Total # of pages, used_pages, and data_pages for a given heap/clustered index
SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) AS TotalPages, 
    SUM(a.used_pages) AS UsedPages, 
    (SUM(a.total_pages) - SUM(a.used_pages)) AS UnusedPages
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

And I found out that the Person.Address table has 689 pages.

So what are those 38 .BCP files?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The data in a BACPAC file is exported from the database using the Bulk Copy (BCP) Utility. From the docs:

The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

BACPAC files contain data that is exported using BCP’s "native" file format, which is a binary type file. The data for the table is simply exported across multiple files, rather than one big file. If you look through the .bcp files across different tables, you’ll find that the file size will vary, with skew in size related to variation in different rows having different data width. For example, when I create a bacpac of AdventureWorks, the .bcp files for Purchasing.PurchaseOrderDetail range from 39kb to 62kb.

BCP (and thus BACPAC generation) does not back up data by copying data pages like a standard backup, but rather is doing table-level querying & exporting.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply