Replicate structure of tabla, procedures, functions in SQLSERVER RRS feed

  • Question

  • Hi

    I would like to create a mirror of a database but not only of data. I would like the same objects and information...

    I  run SSIS packages.. and I would like to have a database similar to another database to only affect a mirror database with the SSIS packages.

    • Edited by neonash Saturday, May 18, 2019 4:28 AM
    Saturday, May 18, 2019 4:25 AM

All replies

  • There are several ways you can do this.

    The simplest and most straightforward method is to backup your database and then restore it on your "mirror" server.

    For more info:

    If for whatever reason you can't do that, and if your database is not too big, you can generate your entire database as scripts.

    Using SQL Server Management Studio (SSMS), you can right-click on a database, and select Tasks > Generate Scripts...

    This will give you the following wizard, which will guide you through the generation of CREATE scripts for all objects within your database:

    Generate Scripts for a Database

    Once the scripts are ready, you can review it and run it wherever you wanted to create the "mirror" database.

    Be sure to click on the "Advanced" button in the "Set Scripting Options" step, and choose "Schema and data" under "Types of data to script". This will also generate INSERT statements for all of the data in your tables:

    Another option is to export your database as a Data-Tier Application. This will create your database as a "bacpac" file, which can then be imported into any SQL Server compatible database.

    To do that, right click on your database in SSMS, and select Tasks > Export Data-tier Application...

    You'll see another wizard which will guide you through the steps.

    Then, in your target database server, you right-click on the "Databases" folder, and select "Import Data-tier Application...":

    Again, you'll get a wizard to guide you through the steps.

    Eitan Blumin; SQL Server Consultant - Madeira Data Solutions

    Wednesday, May 22, 2019 11:27 AM