none
How to perform the Specify Code/tables backup in SQL dwh? RRS feed

  • Question

  • In Azure SQL DWH, i am not seeing the option of export bacpac. Also tried the SSMS generating scripts option and it's failing with below error. Any advise how to achieve the logical backup?

    Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> Microsoft.SqlServer.Management.Smo.UnsupportedVersionException: The 'DwTableDistribution' property is not supported on SqlDatabase edition database of SqlAzureDatabase SQL Server with version 13.0.0. at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ThrowIfPropertyNotSupported(String propertyName, ScriptingPreferences sp) at Microsoft.SqlServer.Management.Smo.Table.GetSqlDwTableCreationScript(ScriptingPreferences sp, StringBuilder sb) at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingPreferences sp) at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

    Monday, November 11, 2019 9:36 AM

All replies

  • Azure SQL DW has a different architecture. For backups we have something called Restore points that are taken automatically for you. 

    Reference:

    https://blobeater.blog/2018/08/07/azure-sql-data-warehouse-and-backups/
    https://www.sqlservercentral.com/articles/backups-in-azure-sql-data-warehouse
    https://docs.microsoft.com/en-us/azure/sql-data-warehouse/backup-and-restore

    For second question, in the wizard - click on Advanced button and just select version as SQL Azure DW.
    It should generate scripts for you:


    Regards,
    Vaibhav


    Monday, November 11, 2019 9:52 AM
  • Hi Everfor,

    What SSMS version are you using? I'm using version 18.3 and the generate script option is working fine for me. 

    Regards.

    Monday, November 11, 2019 1:19 PM
  • After selecting the SQL Dwh, it's failing with below different error

    

    Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptPublishException: An error occurred while scripting the objects. ---> Microsoft.SqlServer.Management.Smo.FailedOperationException: Execute with results failed for Database 'BDASV01'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: There is already an object named '#tempordering' in the database. at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(StringCollection sqlCommands) at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteWithResults(StringCollection query) at Microsoft.SqlServer.Management.Smo.Database.ExecuteWithResults(StringCollection sqlCommands) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.Smo.Database.ExecuteWithResults(StringCollection sqlCommands) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ExecuteQueryUsingTempTable(List`1 objectList, List`1 list, String query) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.OrderAndStoreSchemaBoundInSingleDatabase(List`1 list, String query) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.OrderAndStoreSchemaBound(List`1 schemaboundList) at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveTableViewUDFSprocDependencies() at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.ResolveDependencies() at Microsoft.SqlServer.Management.Smo.SmoDependencyOrderer.Order(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns) at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(Urn[] urns, ISmoScriptWriter writer) at Microsoft.SqlServer.Management.SqlScriptPublish.SqlScriptGenerator.DoScript(ScriptOutputOptions outputOptions) --- End of inner exception stack trace --- at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

    Monday, November 11, 2019 1:23 PM
  • Monday, November 11, 2019 1:43 PM
  • Which ssms version are you using?
    Monday, November 11, 2019 1:44 PM
  • Microsoft.SqlServer.Management.Smo.FailedOperationException: Execute with results failed for Database 'BDASV01'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: There is already an object named '#tempordering' in the database. at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand, Boolean retry) --- End of inner exception stack trace --- at 

    Any idea, where this #tempordering table is being used? 

    As a workaround please try:

    1. Disconnect the SQL connection and reconnect to it, right click on DB and generate script again

    OR

    1. Use select specific database objects option in the wizard and select tables,views, SPs manually or select all and generate


    Regards,
    Vaibhav

    Monday, November 11, 2019 3:07 PM