Copy the schema of a SQL Azure data warehouse?

Question

I want to copy our data warehouse's tables, procs, users, etc. to a new staging DW, but I don't need any of the data.

I've tried creating a snapshot with Redgate SQL Compare and get an error saying DW snapshots aren't supported. I've also tried scripting the DW, but I get an error saying "The 'DwTableDistribution' property is not supported on SqlDatabase edition database of SqlAzureDatabase SQL Server with version 13.0.0".

The only idea I have left is just to restore a full copy of the prod DW and wipe out the data, but that seems like a pretty huge undertaking when all I need is the skeleton of the DW. Any ideas?

Answer 1

You have a few options:

• newer versions of SQL Server Management Studio (SSMS) have support for scripting Azure SQL Data Warehouse now known as Azure Synapse Analytics. Right-click the database in Object Explorer and explore the scripting options, making sure you set the version for Azure SQL Data Warehouse
• mssql-scripter - a command line scripting tool which supports SQL DW. See here.
• Visual Studio 2019 database projects - try importing the database schema into a Data Project which now supports SQL DW.