Automate D365 F&O Database Refresh from UAT to Dev using Azure DevOps Pipeline

Author: Song Nghia

If you work with Dynamics 365 Finance & Operations, refreshing a Dev (Tier-1) environment from a UAT (Tier-2) database is something you probably do often.

Unfortunately, the process is usually manual and painful:

• Downloading large backups (20–30GB+)
• Timeout errors during restore
• "Corrupted data" issues
• SQLDICTIONARY duplicate key errors during DB Sync

To solve this problem, I built an Azure DevOps Pipeline that automatically:

• Downloads the latest UAT database backup from LCS
• Imports the database into the Dev VM
• Runs DB Sync automatically

Once configured, the entire refresh process becomes a fully automated task.

---

Architecture Overview

The automation flow works as follows:

1. Azure DevOps pipeline runs on the Dev VM using a self-hosted agent
2. The pipeline authenticates with LCS
3. It downloads the latest database backup
4. The existing AxDB is removed
5. The new database is imported
6. Services restart and DB Sync runs automatically

---

Step 1 – Install Azure DevOps Agent on the Dev VM

To allow the pipeline to run directly on the D365 Dev VM, we install a Self-Hosted Azure DevOps Agent.

1. Create a Personal Access Token (PAT) in Azure DevOps
2. Download the Azure DevOps agent
3. Extract it to a folder on the VM (for example C:\Agent)
4. Run the configuration script
5. Register the agent with your DevOps organization

It is recommended to run the agent service using the local Administrator account so it has permission to access SQL Server and system drives.

---

Step 2 – Create a Release Pipeline

Next, create a pipeline in Azure DevOps to automate the restore process.

1. Go to Pipelines → Releases
2. Create a New Release Pipeline
3. Add an Empty Job
4. Select your Self-Hosted Agent Pool
5. Add a PowerShell Task

The PowerShell task will run the automation script responsible for downloading and restoring the database.

---

Step 3 – Secure Credentials

Do not store passwords inside scripts.

Instead, create secure pipeline variables such as:

• LcsPassword
• LcsUsername
• ClientId

Mark sensitive variables as Secret so they are encrypted and hidden in logs.

---

Step 4 – Automation Script

The pipeline uses a PowerShell script that performs the following actions:

• Authenticate with LCS
• Download the latest database backup
• Drop the existing AxDB
• Import the new database
• Restart D365 services
• Run DB Sync

The script also improves reliability by:

• Downloading the latest AzCopy automatically
• Downloading the latest SqlPackage
• Downloading the backup to the persistent J: drive
• Retrying DB Sync if it fails the first time

Below is a simplified example of the restore process:

Import-Module d365fo.tools

# Stop services
Stop-D365Environment -All

# Get latest backup
$backup = Get-D365LcsDatabaseBackups -Latest

# Download backup
azcopy copy $backup.FileLocation J:\Backups\latest.bacpac

# Drop database
Invoke-Sqlcmd "DROP DATABASE AxDB"

# Import database
SqlPackage /Action:Import /SourceFile:J:\Backups\latest.bacpac

# Start services
Start-D365Environment -All

# Run DB Sync
Invoke-D365DbSync

---

Result

After setting up this pipeline, refreshing a Dev database becomes a simple automated task.

Instead of spending hours manually restoring databases and fixing sync errors, the pipeline can complete the process in the background with minimal intervention.

What used to take 1–2 days of manual work can now run automatically in about 2 hours.

---

Conclusion

Automating environment refresh in Dynamics 365 Finance & Operations saves time and reduces human error.

Using Azure DevOps pipelines together with d365fo.tools, AzCopy, and SqlPackage provides a reliable way to manage database refresh operations.

---

Contact

If your company needs support with Dynamics 365 Finance & Operations customization, DevOps automation, or system integration, feel free to contact me.

Song Nghia
Technical Manager

Website:
https://www.songnghia.com