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:
- Save the database as
- Rename the file extension to
- 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
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
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
So what are those 38
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.
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 🙂