Search code examples
azure-devopscontinuous-integrationazure-sql-databasecontinuous-deploymentcicd

Table gets promoted with no data to Prod for Azure SQL database through Azure DevOps

I am successfully able to push changes to Azure DevOps for Azure SQL database with the help of below approach :

  1. create a solution and clone your existing Azure DevOps repo in Visual Studio
  2. Import the DB objects from solution explorer and push the solution to Azure DevOps.
  3. Create a pipeline includes steps as build solution/copy/publish artifact
  4. Create a new release pipeline and use "Azure SQL DacpacTask" task and link DACPAC file (which is generated from above step dynamically).

I can see all the Tables, Functions, Views, Stored Procedure promoted to Prod but the tables came out all blank.

Means, all tables came to prod environment but the data did not come with them. I am looking for all the tables with data in my prod env.

Is there any process I missed, looking for the solution on the above problem.

-Thank You!

Solution

  • You can check with the following things:

    1. On the source server from where you import the database into the solution in Visual Studio, ensure the account you are using to import the database has the database level access so that you can view the data in all tables.

      • If you have the access only on part of the tables in the database, when importing the database, it could import the data only in these accessible tables. For other un-accessible tables, it might import empty tables.
      • Similarly, if your access is lower than table level, all the tables might get empty when importing.

    2. After importing the database into the solution, you can check the imported database to ensure the tables is not empty.

    3. After building and generating the bacpac file in Visual Studio, you can try to uncompress the bacpac file to see check whether all tables are existing or empty.

      • Change the extension for the bacpac file from BACPAC (xxx.bacpac) to ZIP (xxx.zip).
      • Uncompress/open the zip file and you should have a data folder that contains a folder for each table exported with data.
      • If you find a folder for a table that means the bacpac file contains the data for that table.

    4. On the target Azure SQL server, ensure the account to publish/import the database has the server level or database level access so that it can write/push all the data into the new database.