Restore BACPAC file to Azure Synapse SQL Serverless Pool Database
There are few bacpac files that are available in Azure Storage account, now the query i have:
- Can i restore these bacpac file(s) to a database created in Azure Synapse Sql Serverless pool.
If this is not possible what are other workaround to extract data from BACPAC files. The requirement is that the users can query these data on need basis (very rare), they don't want data made available all the time.
Please help and provide your suggestion.
One update as seen below in screenshot from Synapse SQL Pool Serverless there is no option to Restore any database
As per this document it is not possible to create the tables in serverless pool. Hence, you will not be able to restore the bacpac file in server less SQL pools. Based on the approach which you have mentioned in your comment. If you specifically want to use serverless pools, you can use the below approach.
Restore the bacpac to Azure SQL server using import Data-tier application option:
Here is the Synapse pipeline JSON to copy all the tables from Azure SQL database o ADLS:
{
"name": "Pipeline 1",
"properties": {
"activities": [
{
"name": "Lookup1",
"type": "Lookup",
"dependsOn": [],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "AzureSqlSource",
"sqlReaderQuery": "SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES\nWHERE TABLE_TYPE = 'BASE TABLE' and TABLE_SCHEMA = 'dbo'",
"queryTimeout": "02:00:00",
"partitionOption": "None"
},
"dataset": {
"referenceName": "AzureSqlTable1",
"type": "DatasetReference"
},
"firstRowOnly": false
}
},
{
"name": "ForEach1",
"type": "ForEach",
"dependsOn": [
{
"activity": "Lookup1",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"items": {
"value": "@activity('Lookup1').output.value",
"type": "Expression"
},
"isSequential": true,
"activities": [
{
"name": "Copy data1",
"type": "Copy",
"dependsOn": [],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "AzureSqlSource",
"queryTimeout": "02:00:00",
"partitionOption": "None"
},
"sink": {
"type": "DelimitedTextSink",
"storeSettings": {
"type": "AzureBlobFSWriteSettings"
},
"formatSettings": {
"type": "DelimitedTextWriteSettings",
"quoteAllText": true,
"fileExtension": ".csv"
}
},
"enableStaging": false,
"translator": {
"type": "TabularTranslator",
"typeConversion": true,
"typeConversionSettings": {
"allowDataTruncation": true,
"treatBooleanAsNumber": false
}
}
},
"inputs": [
{
"referenceName": "AzureSqlTable2",
"type": "DatasetReference",
"parameters": {
"schema": {
"value": "@item().TABLE_SCHEMA",
"type": "Expression"
}
}
}
],
"outputs": [
{
"referenceName": "DelimitedText1",
"type": "DatasetReference",
"parameters": {
"filename": "@concat(item().TABLE_SCHEMA,'_',item().TABLE_NAME,'.CSV')"
}
}
]
}
]
}
}
],
"annotations": [],
"lastPublishTime": "2023-07-18T06:45:47Z"
},
"type": "Microsoft.Synapse/workspaces/pipelines"
}
Then you can create external tables with the ADLS data which is copied from SQL database. For that go to Data -> Linked Select the primary ADLS and select the path which is having the data. Right click on each file and select New SQL Script select Create External Table.
It will fetch the details of the file, click on continue. Provide the required details of serverless database and table name.
The script will generate automatically, and the tables will be created. But you will have to do this step for all the individual files which are copied from SQL database manually to create respective tables.
As a different option based on the requirement you initially stated that the data will be queried on need basis. You can restore the bacpac data using dedicated SQL pool instead of serverless SQL pool. You can directly restore the database in dedicated SQL pool as like Azure SQL server.
