D365FO: Danger Zone - Database Restoration Across Environments

Let's talk about a deceptively simple task that often hides a minefield of complexity: restoring a database from one environment to another—for example, from Production to Test.

As a DevOps engineer, this operation is far from a one-click affair. It involves a series of steps across LCS or Power Platform Admin Center, plus a whole lot of manual work.

⚠️

The Challenge

Database restoration is more complex than it appears, requiring multiple critical steps executed in precise order.

🔧 The Problem: What Needs to Happen

Here's what typically needs to happen during environment restoration:

👥

Re-enable User Access

All users are disabled during the copy process and need to be re-enabled.

🔄

Redirect Integration Endpoints

Avoid sending data to production systems by redirecting to test endpoints.

🔒

Mask Sensitive Data

Ensure compliance and security by masking sensitive information.

📦

Shrink the Database

Remove unnecessary data that isn't needed in lower environments.

...and many more steps—each with its own risks and dependencies.

Traditionally, this is done by connecting to the database via JIT access and running pre-prepared SQL scripts—or worse, manually.

🚀 The Solution: A Smarter Execution Model

We've developed a new model to streamline and safeguard this process. The idea is simple: execute predefined operations in a specific order when AOS starts—but only if the environment URL matches a configured template.

💡

How It Works

When AOS starts (i.e., batch job servers are launched), the system checks for the existence of the batch job AOS Start task processor. If it doesn't exist, the system creates it automatically.

This processor retrieves all records from the AOS start functionality form and evaluates whether the conditions match the current environment. The first check compares the current environment's URL against the Env URL template field.

All processing is executed within the AOS Start task processor batch job.

🧩 Two Types of Executions Supported

📝 SQL Script Execution

Execute SQL scripts directly using statement.executeUpdateWithParameters with built-in limitations. Each script runs only if:

✓ The environment URL matches the configured template
✓ The script is marked as "Run on Start"
✓ The script is allowed to run once or every time AOS starts

⚙️ D365FO Class Invocation

Execute custom X++ classes that implement:

SIMSStartActionRunClassInterface

Classes with run() and resultMessage() methods

SysRunnable

Standard RunBase implementations with a run() method

You can also view the execution history and results for full transparency.

🔐 Security First

🛡️

Approval Workflow

Scripts must be approved before execution. If you're logged in as Admin, you can self-approve. All other users require external approval—no shortcuts.

Only approved lines can be executed. You cannot run your own line unless it's approved by another user. The exception is the admin user, who can approve and run their own lines.

Often, admin is a service account without regular access to the system. To address this, sub admin users can be created by admin and granted full administrative rights within this module.

🎯 Key Features

🌍 Environment-Specific Execution

Tasks execute only when the environment URL matches the configured template. This ensures that:

🔒 Production restoration tasks don't run in development
🔒 Test environment tasks are isolated from UAT
🔒 Each environment has its own configuration

⚡ Flexible Execution Control

Each task can be configured with:

🔢

Sort Order

Determines execution order

▶️

Run on Start

Execute on AOS startup

🔄

Continue on Error

Allow subsequent steps if this fails

🔄

Start Each Time

Run once or every startup

🗑️

Delete Line

Remove after execution

📊 Execution History & Logging

📜
History of transactions
View detailed transaction history for each line
📋
Common log
Displays general logs, including approval actions, deletions, and execution results
👁️
Result tracking
See who ran the line, when, and how
⚠️
Error reporting
Clear indication of whether the last execution resulted in an error

💡 Why This Matters

This small but powerful functionality helps:

⏱️

Save Time

Eliminate manual JIT access connections and script execution

✅

Reduce Errors

Automated, consistent execution every time

🎯

Improve Predictability

Same operations execute the same way across all environments

If you're managing D365FO environments and juggling DevOps tasks, this model might just be your new best friend.

❓ Frequently Asked Questions

How to automate post-database-restore tasks in D365 Finance and Operations?

SIMS Start Processing runs predefined SQL scripts and X++ class methods automatically when AOS starts. Configure your post-restore steps (re-enable users, redirect endpoints, mask data) as lines in the "AOS start functionality" form. When the environment starts after a database copy or refresh, the batch job executes them in order — no manual JIT access or script execution needed.

How to re-enable users after D365FO database copy from Production to Test?

Add a SQL script line that re-enables user accounts (e.g., UPDATE UserInfo SET ENABLED = 1 WHERE ...) in SIMS Start Processing. Configure the Env URL template to match your Test environment. When the database is restored and AOS starts, the script runs automatically — users are re-enabled without manual database access.

How to prevent Production integration endpoints from running in Test environment after database refresh?

Configure SQL scripts that redirect integration endpoints (web service URLs, batch endpoints, external system connections) from Production to Test values. With environment-specific URL matching, these scripts only execute in the correct target environment — Production endpoints are automatically redirected when the database lands in Test.

How does the approval workflow work for SQL scripts in D365FO?

Scripts must be approved before they can execute. Regular users cannot approve their own scripts — another user must approve them. The Admin user can self-approve. Sub-admin users can be created for delegated approval. This ensures no unauthorized SQL runs in your environments, providing an audit trail for compliance.

Can SIMS Start Processing run X++ classes automatically on AOS startup?

Yes. In addition to SQL scripts, you can configure X++ class methods that implement SIMSStartActionRunClassInterface (with run() and resultMessage() methods) or standard SysRunnable classes. These execute in the configured order as part of the AOS startup batch job, with full execution history and error reporting.

What are the best practices for D365FO database refresh automation?

Best practices include: (1) automate user re-enablement, (2) redirect all integration endpoints to test systems, (3) mask or anonymize sensitive/personal data for GDPR compliance, (4) shrink the database to remove unnecessary data, (5) update batch job configurations for the target environment. SIMS Start Processing handles all of these as configurable steps with environment-specific conditions and approval-based security.

⚠️

Warning

This project may involve risks and potentially dangerous operations. Use it at your own risk. Please review the warning and disclaimer in the repository README before implementation.

SIMS Start Processing - D365FO Environment Automation