Why my tables are empty after importing the BACPAC file ????

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

 

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 .

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.