none
Generate and Publish Scripts Wizard limitations - SQL Server 2016-2017

    Question

  • Hi,

    I need to deploy a database having some tens of thousands of tables to another server/instance and I want to use the Generate and Publish Scripts Wizard by SSMS. In particular, I need to generate the database schema.

    If I select all the tables, I've an error about the unexistence of the first selected table (obviously this table is present in database); if I select all the tables without the first one the same error occurs for the first selected table.

    Now, does it exist any limitations for the Generate and Publish Scripts Wizard? About the max number of tables? About memory to have?

    Any helps to me, please?

    Thanks

    Thursday, May 17, 2018 8:35 AM

All replies

  • Hello,

    Why do you want to use script wizard for this? Deployment with DACPAC is easier and do have the benefit, you can also do a Delta deployment = deploy changes maid since the last deployment

    Extract, Publish, and Register .dacpac Files


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Visakh16MVP Thursday, May 17, 2018 9:38 AM
    • Unproposed as answer by pscorca Thursday, May 17, 2018 9:47 AM
    Thursday, May 17, 2018 8:53 AM
    Moderator
  • Hi Olaf,

    thanks for your suggest, but I've tried to use DACPAC unsuccessfully.

    However, it is very useful to know the script wizard limitations, if possible.

    Many thanks

    Thursday, May 17, 2018 8:55 AM
  • Hi Olaf,

    thanks for your suggest, but I've tried to use DACPAC unsuccessfully.

    However, it is very useful to know the script wizard limitations, if possible.

    Many thanks

    Question is what was the issue you faced with DACPAC

    If you can give the details, may be we can help to get it sorted

    deploy a database having some tens of thousands of tables to another server/instance 

    Sounds like a classical use case for DACPAC


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, May 17, 2018 9:39 AM
  • Ok, however it could be useful to know the eventual limitations of the script wizard.

    Now, for extracting data and structure from a corrupted database can I produce a DACPAC as the right way to follow?

    Thanks

    Thursday, May 17, 2018 12:53 PM
  • Wizard has limitation. My feeling is it is Only useful for simple toy tables. For importing large tables across different platforms wizard will crash elegantly. I been thru it. 
    Thursday, May 17, 2018 2:30 PM
  • other option: Use SSDT (Download SQL Server Data Tools (SSDT)) , create a database project (How to: Create a New Database Project), import the database schema, then you can create a deployment script.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, May 17, 2018 4:05 PM
    Moderator