Copy the schema of a SQL Azure data warehouse?

2
votes

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?

1
sql-serverazure-sql-databasedata-warehouseazure-sqldwazure-sql-data-warehouse
though question sounds little confusing, but i reached to solution I was looking for. - piyushmandovra

1 Answers

7
votes

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.