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 window:

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 are available under the Advanced options: Schema mapping, Object filter, Package 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 users 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:

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

Here, ApexSQL Diff allows users 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 panel:

After finishing the SQL objects synchronization fine tuning, the synchronization of the scripts and the live SQL database can commence with the click on the Synchronize button from the Home tab:

After the first step, in which direction of synchronization is set, in the second step, ApexSQL Diff will check dependencies:

The next 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:

In the last step, ApexSQL Diff generates Summary and warnings, a step 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:

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

How to create a SQL database build script directly from a backup

ApexSQL Diff has the ability to compare and synchronize a database backup against a script folder, which could be of great help during the development process. It might be needed to use the backup instead of the live database to create a SQL database build scripts, and this is where ApexSQL Diff can help. There is no need to waste time and resources on restoring the SQL Server database backup. ApexSQL Diff enables users to save a database schema as a set of object creation scripts directly from your backup.

March 2, 2015

How to refactor a SQL Server database table to add a surrogate key

Choosing a primary key is one of the most important steps in a database design. There are two types of a primary key – a natural key and a surrogate key.

In this article it will be described how to apply database refactoring on a SQL Server database and replace a natural key with a surrogate key by using ApexSQL Refactor a SQL Server Management Studio and a Visual Studio add-in that provides numerous features for refactoring of SQL code and uses one of fastest SQL formatters.

February 27, 2015

SQL query basics – How to improve readability by formatting commas, spacing and alignment

The previous article covered SQL query readability basics such as capitalization strategies and their implementation in SQL formatter by ApexSQL. This time, commas, spacing, and aligning will be detailed. One of the quickest ways to wreak havoc among developers is to start a discussion about how commas should be treated within the code, particularly in a SELECT list. Let’s look at how commas can be treated in ApexSQL Refactor.

February 27, 2015

SQL query basics – How to improve readability by formatting capitalization and object naming

Many development teams spend an inordinate amount of time arguing over styling and formatting preferences. Although these preferences are often subjective, at the end the code should be consistent. Since styling comes up frequently during code reviews, it is a good idea to have a strategy in place for dealing with it. This article series will address several SQL readability strategies as well as provide examples that demonstrate different ways you can format T-SQL in ApexSQL Refactor. Let’s begin with capitalization and object naming.

February 27, 2015

Replacing one-to-many relationships with association tables automatically– Part 1

The Replace one-to-many relationship feature in ApexSQL Refactor, an SSMS/VS add-in, will isolate the relationship between two tables and add a separate association table.

A one-to-many relationship is one of the most common relationship types in relational databases. It is a principle of database design, implicitly created and enforced by the use of relationships between tables, especially the relationship between a primary key and a foreign key.

February 27, 2015

How to safely modify SQL stored procedure and SQL function parameters

Modifying parameters of a SQL function or a stored procedure is not a simple task if dependencies need to be kept and to maintain a database integrity. ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in for SQL formatting with a collection of useful code refactors. In this article, one particular refactor for safe parameter modification will be described.

February 27, 2015

How to format a SQL query to qualify object names explicitly

ApexSQL Refactor is a SQL Server Management Studio and Visual Studio add-in, which formats SQL query and refactors SQL code using 11 code refactors and more than 200 formatting options.

The Object name qualifying feature allows users to refactor a SQL script so that all object names are qualified. The feature can refactor object names by qualifying these in a way to add:

February 27, 2015

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.

February 24, 2015

How to code faster with SQL Server code snippets

ApexSQL Complete is a SQL Server Management Studio and Visual Studio add-in designed to speed up the coding. It completes SQL statements automatically, fills in SQL fragments, generates outlines for the SQL script structure, and speeds up coding with T-SQL code snippets.

February 24, 2015

The art and science of creating SSMS 2012 add-ins- Part 3 – Creating Toolbars and new Windows

In the previous two parts of our series, we worked on foundations of an SSMS 2012 add-in. We talked about the mechanism that is behind loading add-ins into SSMS, about the add-in file and the creation of basic Visual Studio 2012 add-in project. We also discussed main parts of the extensibility API and setting up debugging of our add-in. In this article, we‘ll expand that story with the steps to further modify SSMS UI, by creating toolbars and new windows. We also take a look at the method for controlling commands appearance in SSMS 2012

February 20, 2015

The art and science of creating SSMS 2012 add-ins – Part 1 – Laying the Foundation

This article is a tutorial for creating SQL Server Management Studio 2012 add-ins. We will show you how to create and debug a simple SSMS add-in using Microsoft Visual Studio 2012, and explain the main parts of the add-in structure in more detail. In the first part of this article we will cover the processes of creating a Visual Studio add-in project and loading the add-in into SSMS, and we will describe the structure of the .Addin file. We will also cover the process of setting up debugging for the add-in project. In the second part of the article we will cover the code generated by Visual Studio, main parts of the Connect class and we will go through the process of creating simple menu items for the add-in

February 20, 2015

How to restore specific table(s) from a SQL Server database backup

If restoration of an original database to a previous state is not an option, in the case of accidentally updated, dropped, deleted, damaged data, it is still possible to restore these tables if a database backup was created prior to the accident.

February 4, 2015

SQL Server Source control – Part I – understanding source control basics

The goal of database source control is to propagate changes from a development environment, to test and production without issues and to fulfill the need to restore a database at any point in time, maintaining an audit trail, and to allow successful team collaboration during the project.

February 3, 2015

How to document SSIS package control flow

Control flow is the SQL Server workflow engine that contains control flow elements. An SSIS package consists of at least one control flow task, and optionally one or more data flows.

February 2, 2015