In the world of cloud-native applications and data services, it is increasingly common to migrate or synchronise specific data subsets between Azure SQL database. A typical use case is copying a specific schema from one Azure SQL Database to another without using traditional BACPAC imports, full database backups, or ETL tools. 

Sometimes, we need to copy only a part of data (like one section or table) from one Azure SQL Database to another. This might be for testing, backups, or reporting. Instead of copying the whole database, we can use a simple and secure method using PowerShell and SQL commands.  

Use Case: Schema-Specific Copy Between Azure SQL Databases 

We were tasked with copying all the data from a specific schema—Reference—from the source Azure SQL database db02 to the destination db04. Instead of exporting the whole database or writing complex integration logic, we leveraged external data access features in Azure SQL to read data directly from the source into the destination. 

What Is PolyBase? 

PolyBase is a feature in SQL Server that enables you to query external data sources as though they were regular, local tables. While Azure SQL Database doesn’t support PolyBase in the traditional sense, it supports similar functionality using External Tables combined with External Data Sources and Database Scoped Credentials. 

Exploring a career path in SQL?
Get started with our Oracle SQL Training

What Is an External Table? 

An external table is a special SQL object that maps to a table in a remote database. It acts as a bridge — letting you query or join remote tables directly as if they were part of your local database. 

What Is an External Data Source? 

An external data source defines how to connect to a remote data system. It contains the remote server name, database name, and the associated credentials needed to authenticate. 

What Is a Database Scoped Credential? 

A database-scoped credential stores username and password information used by external data sources. It is scoped to the database and ensures secure, reusable authentication for accessing external systems. 

PowerShell Script 

The following PowerShell script demonstrates the full process: 

# Define source and destination details 

$destinationServer = "tsql02picuk.database.windows.net" 
$destinationDatabase = "db04" 
$sourceServer = "gsql01picuk.database.windows.net" 
$sourceDatabase = "db02" 
 
# SQL Auth Credentials 

$AdminUser = "admin" 
$AdminPass = ConvertTo-SecureString "XXXXXX" -AsPlainText -Force 
$Cred = New-Object PSCredential ($AdminUser, $AdminPass) 
 
# SQL command block 

$sqlCommand = @" 
-- Create credentials to authenticate to source 
IF NOT EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = 'RemoteDBCredential') 
BEGIN 
    CREATE DATABASE SCOPED CREDENTIAL RemoteDBCredential 
    WITH IDENTITY = 'admin', SECRET = 'XXXXXX; 
END 
 
# Create an external data source 

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'RemoteDBSource') 
BEGIN 
    CREATE EXTERNAL DATA SOURCE RemoteDBSource 
    WITH ( 
        TYPE = RDBMS, 
        LOCATION = '$sourceServer', 
        DATABASE_NAME = '$sourceDatabase', 
        CREDENTIAL = RemoteDBCredential 
    ); 
END 
 
# Create an external table 

IF NOT EXISTS (SELECT * FROM sys.external_tables WHERE name = 'ExternalReferenceTable') 
BEGIN 
    CREATE EXTERNAL TABLE Reference.ExternalReferenceTable ( 
        COLLAT_TYP, 
        Mapping, [ID] [int] 
    ) 
    WITH ( 
        DATA_SOURCE = RemoteDBSource, 
        SCHEMA_NAME = 'Reference', 
        OBJECT_NAME = 'LookupCollateral' 
    ); 
END 
 
# Transfer data into the local target table. 

SET IDENTITY_INSERT Reference.LookupCollateral ON; 
INSERT INTO Reference.LookupCollateral (COLLAT_TYP, Mapping, ID) 
SELECT COLLAT_TYP, Mapping, ID 
FROM Reference.ExternalReferenceTable; 
SET IDENTITY_INSERT Reference.LookupCollateral OFF; 
"@ 
 
# Execute the SQL on the destination DB 

try { 
    Write-Output "🔄 Executing SQL commands on $destinationDatabase..." 
    Invoke-Sqlcmd -ServerInstance $destinationServer -Database $destinationDatabase -Credential $Cred -Query $sqlCommand 
    Write-Output "✅ Data copied successfully!" 
} catch { 
    Write-Error "❌ Error: $_" 
    throw $_ 
} 

Why This Approach Works Well 

Key benefits include: 

• Secure – Uses database-scoped credentials instead of plain connections 
• Fast – Reads directly from the source, no intermediate staging required 
• Reusable – Change table/schema names and rerun 
• Minimal Downtime – No need to export/import large files 
• Azure-Native – Fully compatible with Azure SQL DB’s capabilities 

Final Thoughts 

This method of copying data across Azure SQL databases using external tables and PowerShell is efficient and repeatable. It avoids the overhead of full exports or the complexity of external ETL tools. With some enhancements, this could even become part of a CI/CD pipeline or automated data refresh process. 

Author: Mayank Minal is a Principal Consultant at Fusion Practices