Load data from one database to another database in ADLA

Currently in U-SQL, there is no support for backup and restore of databases. So, if you are having a production database and want to restore the data from production to development, you dont have easy mechanism to do it.

Below powershell script will be helpful to copy all data from production database to development database, provided both are having same schema.

Make sure that you are doing deployment in both development and production database and they are in same schema for tables.

The below powershell script generates the TRUNCATE TABLE & INSERT INTO script for all the production database tables. This genrerated script can be run as a job in ADLA development account. Replace the highlighted values with your details accordingly.  

Connect-AzureRmAccount -SubscriptionName "YOUR_SUBSCRIPTIONNAME"

$usqlScriptForTablesBackup = ""

$tables = @()

$databaseName = "PRODUCTIONDB"

$tables = Get-AdlCatalogItem -Account "PRODUCTION_ADLA_Account" -ItemType Table -Path $databaseName |  ForEach-Object { [pscustomobject]@{schema=$_.SchemaName; table=$_.Name} }

foreach($table in $tables)

{

$usqlScriptForTablesBackup += "TRUNCATE TABLE {0}.{1}.{2}; INSERT INTO {0}.{1}.{2} SELECT * FROM PRODUCTION_ADLA_Account.{0}.{1}.{2};`r`n" -f "PRODUCTIONDB",$table.schema, $table.table

}

$databaseName = "DEVELOPMENTDB"

$tables = Get-AdlCatalogItem -Account "PRODUCTION_ADLA_Account" -ItemType Table -Path $databaseName |  ForEach-Object { [pscustomobject]@{schema=$_.SchemaName; table=$_.Name} }

foreach($table in $tables)

{

$usqlScriptForTablesBackup += "TRUNCATE TABLE {0}.{1}.{2}; INSERT INTO {0}.{1}.{2} SELECT * FROM PRODUCTION_ADLA_Account.{0}.{1}.{2};`r`n" -f "sources",$table.schema, $table.table

}

$usqlScriptForTablesBackup | Out-File -FilePath "D:\backupscript.usql"