Best Methods for Restoring Azure SQL DB to a Lower Environment

Given the unknowns with your database size and speed of operation, I’d recommend the safest route:
- Make a copy of the database.
- Use Sqlpackage for export and import.

By doing this, you ensure that you aren't losing any critical data.

You might want to use the 'create database as copy' syntax. Check out the official Microsoft docs for details; it's pretty straightforward but does the job well.

I just recently tackled this and found that exporting a .bacpac using both the GUI and sqlpackage was quite the hassle. The easiest route I discovered was using SQL copy.

Here’s my streamlined process:
1. SQL copy the database from Prod to your lower environment and set the lower environment DB SKU higher initially for a quicker copy.
2. Use Azure Data Factory to obfuscate PII.
3. Finally, scale down the database for the lower environment.

My Prod DB is around 400GB, and the whole operation takes roughly an hour, hitting it nightly with minimal impact on the source DB. BACPACs had issues since my production DB is always in use, leading to inconsistencies.

Before proceeding, consider a few important questions:
1. What’s the size of your database?
2. Do you need to transfer all data, or can you get by with just a portion?
3. Is there any sensitive data that needs to be obfuscated due to GDPR or other regulations?
4. How are you connecting to your database?
5. Would you prefer to automate this process to run regularly at a specific time, or do it on-demand?

From my experience with similar situations, we had to take a backup, move data between storages due to regulations, and clean a large chunk of data for compliance. The orchestration can be complex based on your requirements.