How to copy Azure database from Elastic Business Critical to Standard edition using ADF or T-SQL?

Question

How to copy Azure database from Elastic Business Critical to Standard edition using ADF or T-SQL?

I'm trying to copy an Azure SQL Database from an Elastic Pool in the Business Critical tier to a Standard (DTU-based) edition using T-SQL. However, the following attempt fails with the error:

Msg 40808, Level 16, State 1, Line 10: The edition 'BusinessCritical' does not support the service objective 'S2'.

It seems that the direct copy of a database from Business Critical to Standard via T-SQL isn't supported.

Question: Is there a supported way to achieve this ideally using TSQL or Azure Data Factory (ADF) without incurring high temporary costs from Business Critical?

Thanks in advance for your guidance.

Chandra Boorla 13,790 Reputation points Microsoft External Staff Moderator

2025-06-13T14:39:04.1266667+00:00

@Katarzyna Miskowiec - Administrator

Just checking in to see if the below answer provided by @ Marcin Policht helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

Chandra Boorla 13,790 Reputation points Microsoft External Staff Moderator

2025-06-13T14:39:04.1266667+00:00

@Katarzyna Miskowiec - Administrator

Just checking in to see if the below answer provided by @ Marcin Policht helped.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Try one the following:

Option 1: Use Azure Data Factory (ADF). This provides a fine-grained control, it's not performance-tier dependent, and can move just data

Create an empty target database in the Standard (DTU) tier (S2, for example).
Use ADF Copy Data Activity to copy:
- From: Source Azure SQL DB (Business Critical, Elastic Pool)
- To: Target Azure SQL DB (Standard DTU-based)
Select:
- Source/Target linked services using SQL authentication
- Auto-create tables or use pre-created schema
- Use staging if required (for large datasets)

ADF can copy schema and data, but you may want to pre-create indexes, constraints, etc., if you need 100% parity.

Option 2: Export/Import BACPAC via Azure Storage. This gives you full compatibility with any tiers and doesn't involve downtime

Export the source DB to a BACPAC file (in Azure Blob Storage).


   -- In PowerShell or Azure CLI (not T-SQL), use:

   az sql db export --name your-db-name --resource-group your-rg \

   --server your-server-name --storage-key-type StorageAccessKey \

   --storage-key "your-key" --storage-uri "https://yourstorage.blob.core.windows.net/container/your.bacpac"

Import that BACPAC into a new Standard-tier database:


   az sql db import --name new-db-name --edition Standard --service-objective S2 ...

You can delete the BACPAC after import, and you won't incur extra Business Critical costs during the copy — Azure handles the resource movement in the backend.

Option 3: Geo-Restore or Point-in-Time restore (this would involve an extra cost short term)

Restore the Business Critical DB to a General Purpose (vCore) or Standard (DTU) tier first
Then do the copy
Drawback: Requires temporary storage of a full DB in an intermediate tier, may incur higher costs briefly

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Katarzyna Miskowiec - Administrator 0 Reputation points

2025-06-12T14:55:00.4133333+00:00

Thanks for your quick reply, Marcin.

I still have a couple of concerns:

What happens with other database objects like stored procedures, functions, indexes, and users? Will they also be copied?

Also, I’d prefer to avoid moving the data through intermediate places if possible.

Thanks!
Marcin Policht 49,005 Reputation points MVP Volunteer Moderator

2025-06-12T15:44:05.53+00:00

Azure Data Factory (ADF) focuses only on data — i.e., table rows. If you're using ADF, you'll need to pre-script and deploy the schema (procedures, functions, views, etc.) using something like:

SQL Server Management Studio (SSMS) Generate Scripts

Visual Studio DACPAC (SQL Project)

sqlpackage.exe

Azure DevOps Pipelines

BACPAC Export/Import contains schema (tables, views, stored procs, functions, triggers) and data, but not logins, role memberships, and permissions. So it's more complete than ADF, but still misses security metadata.

To provide full fidelity with no intermediate transfers, consider using sqlpackage.exe with the Extract/Publish model (DACPAC), which:

Keeps everything local between source and destination (no intermediate storage unless explicitly used)

Can include schema and data

Supports pre/post deployment scripts

Extract from Source DB (Business Critical):

sqlpackage.exe /Action:Export /SourceConnectionString:"..." /TargetFile:Source.dacpac

Deploy to Target DB (Standard S2):

sqlpackage.exe /Action:Publish /SourceFile:Source.dacpac /TargetConnectionString:"..." /p:BlockOnPossibleDataLoss=false

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Share via

How to copy Azure database from Elastic Business Critical to Standard edition using ADF or T-SQL?

1 answer

Your answer