locked
backup sql schema to files to be used in building db RRS feed

  • Question

  •  I have seen a lot of scripts where you backup tables,sp's, views, but does anyone have something that applies these
    files to build a new DB when that schema has foreign keys?

     how do order the applying of files?

     Thanks.

    • Moved by jrv Tuesday, May 22, 2018 12:31 AM correct forum
    Tuesday, May 22, 2018 12:15 AM

Answers

  • Hi hart60,

    DAC is good way to copy a SQL database with just the objects and no data.

    You can also try to use T-SQL to generate a script for the creation of all Foreign Key constraints.

    See: Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server database using T-SQL

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by hart60 Thursday, May 24, 2018 6:19 PM
    Tuesday, May 22, 2018 6:05 AM

All replies

  • I moved this to the SQLServer Migration forum as it is a SQLServer specific issue and not a PowerShell issue.

    \_(ツ)_/

    Tuesday, May 22, 2018 12:33 AM
  • Not sure exactly what kind of scripts you are talking about. 

    Another option is to explore Sql server Data tools/sqlpackage to build a database from dacpac file which will take care of all ordering while creating schema.

    • Proposed as answer by Pirlo Zhang Tuesday, May 22, 2018 6:05 AM
    Tuesday, May 22, 2018 3:44 AM
  • Hi hart60,

    DAC is good way to copy a SQL database with just the objects and no data.

    You can also try to use T-SQL to generate a script for the creation of all Foreign Key constraints.

    See: Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server database using T-SQL

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by hart60 Thursday, May 24, 2018 6:19 PM
    Tuesday, May 22, 2018 6:05 AM
  •  Thanks for the suggestions.

    Thursday, May 24, 2018 6:19 PM
  • Thursday, May 24, 2018 6:44 PM
  •  Thanks will check into using this approach.

    Thursday, May 24, 2018 7:40 PM
  • In case if you are generating the schema script for Azure SQL DB, you can use our Data Migration Assistant tool (DMA) to generate the schema and deploy to Azure SQL DB.

    https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql

    • Proposed as answer by jrv Sunday, June 10, 2018 6:06 PM
    Sunday, June 10, 2018 6:01 PM
  • In case if you are generating the schema script for Azure SQL DB, you can use our Data Migration Assistant tool (DMA) to generate the schema and deploy to Azure SQL DB.

    https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql

    The DMA can also upgrade SQL databases to a newer version of SQS.  It is not just for Azure migrations. The plus with DMA is that it can upgrade the schema during migration.  It can target Azure and, at least, SQS2012 and later databases and source from SQS2005 and later.

    For full specs and capabilities see: https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-2017


    \_(ツ)_/

    Sunday, June 10, 2018 6:10 PM