How to build a SQL database directly from scripts

In a SQL database lifecycle two major deployment tasks are: the building of a new SQL database and an upgrade of the existing one. ApexSQL Diff is a SQL database comparison tool that can help for both of them; it allows deploying SQL scripts saved in a script folder to a new or an existing SQL database. In various “real world” scenarios oversights and omissions are plausible, leading to many errors or database structure/objects corruption. In cases like these, it’s good to have a script ready so that a SQL database with test objects can be easily rebuilt even if something goes wrong.

How can ApexSQL Diff help with these SQL script deployment and database SQL database building tasks?

To build a SQL database directly from the script folder used as the source simply create a new SQL database, leave it blank and use it as the comparison and synchronization destination. ApexSQL Diff will synchronize it by creating all objects from the script folder used as the source.

The process is similar when upgrading a SQL database, except that the destination will be a SQL database that already has some objects. When starting ApexSQL Diff and opening a new project, the available options are shown in the New project dialog.

Selecting data source types and details

By selecting the Script folder as a data source type and setting up the script folder location, the script source is set up and all that remains is to select the destination data source. Additional options available are Schema mapping, Object filtering and Additional scripts.

Schema mapping enables specifying how owners/schemas are handled during the SQL databases comparison. By default, schemas of the same name are automatically mapped to each other.

The Object filter allows comparison and synchronization of only specific SQL objects, a useful option when updating a live SQL database from a backup or test script on a daily basis. It is an invaluable feature when it comes to saving time, especially in cases of a large number of database objects.

Selecting SQL object types or specific objects to compare

Once all the options are set, click the Compare button and the process leads to the main grid window containing the comparison result.

SQL comparison results - Main grid view

Here, ApexSQL Diff allows further synchronization tuning by selecting SQL objects needed to update a live SQL database from a script folder as a source. It is even possible to track differences per individual object on a script level by using the Script difference view pane.

After finishing the SQL objects synchronization fine tuning, the synchronization of the scripts and the live SQL database can commence by using the Synchronize command.

Synchronization wizard - synchronization direction

ApexSQL Diff will check dependencies:

Synchronization wizard - checking dependencies

and generate Summary and warnings, a dialog showing synchronization summary and potential problems or objects that cannot be scripted and/or synchronized, and allowing to preview the impact of the synchronization script before executing it.

Synchronization wizard - summary and warnings

The final step before a synchronization script is generated offers a possibility to save a copy of the synchronization script. Saving a copy of the synchronization script is a good way to keep track of updates and deployments over a SQL database, and post-synchronization analysis, if necessary.

Synchronization wizard - output options

The process described here is a synchronization using a script folder as a data source and a live SQL database as a destination, offering an efficient solution to build a SQL database and synchronize its structure and objects to another database through the highly customizable synchronization procedure.

Useful resources:

How to: Generate a Script (SQL Server Management Studio)
Generate and Publish Scripts Wizard
How to: Deploy Changes to New or Existing Databases

March 2, 2015