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.
The following steps explain the complete process:
-
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 database tab:
-
Once the data sources are selected, further options offer additional scripting settings. Select the Options tab where Structure and Data options allow users additional settings for the Structure (DDL) or Data (DML) mode.
The Structure options contain the structure mode scripting settings like Create database, for example, which allow users adding the Create database statement at the top of the output script when scripting to a single file, or Login with users, which scripts SQL Server logins that are used by the scripted database users, etc.
Under the Data section 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 the Schema mapping, it is easy to replace the schema with a new one:
Or, using the Owners in script option in the Options tab, include/exclude schemas from object names:
-
Another possible case may be scripting multiple data sources without an actual need to script all the objects, but only specific ones. Switch to the Object filter tab and it will show detailed object filtering and selection that 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, click the Load button and the selected objects and data are shown in the main application window, 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:
Also, when the Data view is selected from the Home tab, specific object data can be included/excluded 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 from the Home tab, the Script wizard will be initiated:
-
In the first step, select the Scripting mode and for the purpose of this article the Structure and data mode is selected:
-
In the second step, SQL script will be selected as the output type:
-
In the next step, 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 SQL script options. This means that final script can be saved to file(s) or opened in the selected editor:
-
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
