0

I have read about SqlPackage tool, and I found it amazing, very useful. I'm struggling though to understand how it can work with the following scenario.

I have an Azure SQL DB, let's call it "MyDb", in a production environment, version 1.0. I would like to spin up a new environment. This new environment will a fresh "MyDb" with an updated schema, version 1.5, and no data.

"MyDb" can indeed receive schema updates and I would like to test my application using this DB with data coming from production. I would like to know if SqlPackage can migrate data from "MyDb" v1.0 to "MyDb" v1.5, keeping the schema in the target but migrating the data from source (v1.0) to target (v1.5).

I quickly tested SqlPackage, but it works rather in the opposite direction, basically that "MyDb" v1.0 receives the schema updates from "MyDb" v1.5. This would mean original data is kept and schema updated, but in my opinion the procedure would be cumbersome, with too many steps:

  1. Create a copy of "MyDb" v1.0 from the production environment in the test environment
  2. Create a new "MyDb" v1.5 in the test environment;
  3. Run SqlPackage to apply schema changes, with "MyDb" v1.5 as source and "MyDb" v1.0 as target;
  4. Destroy "MyDb" v1.5 in the test environment.

Perhaps this is the ideal sequence and I can't expect too much from this tool. In my mind, I thought this would be possible:

  1. Create a new "MyDb" v1.5 in the test environment;
  2. SqlPackage updates "MyDb" v1.5 with the data from "MyDb" v1.0 from production environment, keeping the schema in "MyDb" v1.5 intact. It would also "figure out" how to migrate data if there are schema changes, but maybe I'm too optimistic.

Thank you.

Alberto Solano
  • 101
  • 3

1 Answers1

1

via this activity you're testing your application with non-prod data I think you can consider one of the following options.

  1. Point in Time restore - You can basically create a copy of your production database, test the application and then just delete the database.

https://azure.microsoft.com/en-us/blog/azure-sql-database-point-in-time-restore/

  1. If you're looking to just copy the objects you can just script out the schema and run it on the target database, refer to this post for more information : https://learn.microsoft.com/en-us/answers/questions/1376540/copy-a-azure-sql-db-with-just-the-objects-and-no-d as a second step to this, you can also explore azure data sync or data factory (again if it is just for a testing, PITR would be the best option.)
Abhishek Shaha - MSFT
  • 11
  • 1