How to combine objects and data from multiple SQL databases in a single SQL script

In some cases a development team may decide to migrate databases between environments by generating a deployment script from multiple SQL databases (development/testing). They can use a tool like ApexSQL Script to create a single script from multiple sources, making it easy to apply to the target environment or the source control. Using this feature, there’s no need to script one SQL database at a time, as multiple data sources are processed simultaneously.

Selecting databases as data sources is the first step towards creating the script; this selection is available in the New project window, under the Select databases tab:

Once the data sources are selected, further options offer additional scripting settings. Using Structure script and Data script options allows additional settings for the Structure (DDL) or Data (DML) mode.

The Structure script option contains the structure mode scripting settings like Script CREATE DATABASE, for example, which allows adding a CREATE DATABASE statement at the top of the output script when scripting to a single file, or Script logins with users, which scripts SQL server logins that are used by the scripted database users, etc.

Under the Data script tab multiple options concerning data scripting are available; for example, if there’s a need to Script rows as INSERT with existence check, ApexSQL Script will add a check for data existence prior to INSERT statements, and only new rows will be inserted on script execution:

Using Schema mapping, it is easy to include/exclude schemas from object names, or replace the schema with a new one:

Another possible case may be scripting multiple data sources without an actual need to script all the objects, but only specific ones. The Object filter option will allow detailed object filtering and selection, and can speed up the process by filtering database objects by object types and object names and running ApexSQL Script only on a subset of objects.

When all the options for scripting are set, the selected objects and data are shown in the main grid, under the Structure and Data tabs respectively, allowing precise and detailed overview of all the objects and data to be scripted. Here, it is possible to select each specific object individually, and include/exclude it from the final script:

Once the selection is made, and all objects needed are included in the scripting process, there are a few simple steps left to complete combining multiple sources into a single script. With a click on the Script button and selecting the scripting mode (Structure, Data, Structure and data), ApexSQL Script will analyze objects and check their dependencies, an important step especially when combining multiple data sources into one script file. At this point, it is possible to check all dependencies, and exclude unnecessary ones:

The final step is selecting the output: writing to a file or creating and checking in directly into a Source Control. After this the script is generated using multiple data sources with customized options and selected objects and data.

ApexSQL Script’s engine offers precise and user-friendly way to script SQL database, combining data sources or using a single one. Every session can be saved and reloaded, further customized and adjusted as users see fit, making it an invaluable tool for many scripting operations.

Useful links:
Generate scripts
Generate and publish scripts wizard
Create script to copy database schema and all the objects

February 24, 2015