Backup/Restore and collation update in Azure Synapse

Question

We have one requirement that to change the collation of Azure SQL serverless database in Azure Synapse Analytics to default collation. So I have 2 questions, 1. How to take backup and restore Azure SQL serverless database in synapse. 2. How to update collation of SQL serverless database.

Answer 1

Hi @Shailesh Ganesh Shripati

Thanks for the question and using MS Q&A platform.

Unfortunately, currently there's no direct way to backup and restore serverless SQL databases in Azure Synapse Analytics. This functionality is not yet supported.

Here's a breakdown of your questions:

1. Backup and Restore of Serverless Database:
   • While dedicated SQL pools in Synapse offer backup and restore functionalities, serverless databases lack this feature.
   • You can suggest this feature by submitting feedback on the Azure Feedback portal: https://feedback.azure.com/d365community/forum/9b9ba8e4-0825-ec11-b6e6-000d3a4f07b8
2. Updating Collation of Serverless Database:
   • Modifying the collation of a serverless database directly isn't possible.
   • Create a New Serverless Database with Default Collation:
   • Define a new serverless database with the desired default collation.
   • Migrate your data and schema to the new database using techniques like Data Factory copy activities or T-SQL commands (depending on data size and complexity).
   • Decommission the old serverless database after successful migration.
3. Consider Dedicated SQL Pool (if applicable):
   • Evaluate if a dedicated SQL pool can fulfill your needs. Dedicated pools offer backup and restore functionalities and potentially more control over configurations like collation.
   • If a dedicated pool suits your requirements, migrate your serverless database to a dedicated pool with the desired collation.

Hope this helps. Do let us know if you any further queries.

---

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.