I worked on a service request that our customer reported that some tables are empty after importing the database using BACPAC file . in this article i would like to share with you my findings and the troubleshooting steps :
1-Compare the number of rows for each table on the source and destination database using the following T-SQL to identify which tables that don't contain data :
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
2-After confirming that the tables don't have data ,we need to check if the exported BACPAC file have data on those tables and this done using the following steps:
- Change the extension for the backup file from BACPAC to .zip .
- Open the zip file and you should have a data folder that contains a folder for each tables exported with data.
- if you find a folder for the tables that means that the BACPAC file contains the data for the tables
3-Connect with the same user that has exported the database and select the data from the tables that have the issue to be sure that the user has permission to view the data on those tables.
As a result, when you are using Row level security and want to export the database please be sure that the user who export the database has permission to view the data on the database tables otherwise this will only export the tables with the data that the user has permission to view .
