Time and again, in many projects so far, we faced the problem of not being able to repeatedly say Azure Synapse Analytics Serverless SQL Pool databases 10 times in succession, but also not being able to deploy them from one environment to another.
SQL scripts can be gitted in Synapse during development, but the reality is often different. Many developers still write their code for the Serverless SQL Pool in the good old SQL Server Management Studio. In other environments there are other data lakes, which often do not have the data structures at the time of deployment that the SQL scripts expect during execution. And Azure Synapse Analytics Serverless SQL Pools currently do not support SQL Database projects.
Since this has repeatedly presented us with challenges in the past, we came up with RhinoBackup, a tool that can both backup and restore Azure Synapse Serverless SQL Pool databases. DROP and CREATE scripts are created for the individual SQL objects and saved as SQL files. In the current version, all scripts of the self-created SQL objects are created and can later be restored to a new server. A configuration file can be used to redefine the storage accounts when restoring, so that the database in the new environment also points to a new data lake.
Currently the following SQL objects can be exported:
- Credentials (without Secrets)
- External Datasource
- Views
- External Tables
- External File Format
- Schemas
- Functions
- Stored Procedures