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

Question

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.

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

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?

asked 2021-08-16 by Francesco Mantovani

---

Answer

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.

answered 2021-08-16 by Andy Mallon