ApexSQL Build: Using custom database post-deployment scripts

When a SQL database is created or upgraded, data may need to be added, changed, or deleted. Additionally, certain actions may have to occur in the SQL database after the process completes, like permissions change, creating additional objects, users, or creating a script to send an e-mail containing a deployment notice, etc. Using ApexSQL Build’s custom post-deployment SQL scripts can be included in the process to accomplish this task.

After setting up the source, properties and objects to be included in the build, ApexSQL Build opens a dialog to include additional scripts to run immediately after the generated SQL script:

One practical example would be creating and adding a SQL script which will send an e-mail containing a notice about a successful (or unsuccessful, if necessary) deployment:

DECLARE @Xml TABLE (XmlData XML);

INSERT INTO @Xml (XmlData)
SELECT *
FROM OPENROWSET(BULK 'c:SamplesReport.xml', SINGLE_BLOB) O;

SELECT [Row].value('Type[1]', 'Varchar(255)') AS Type,
 [Row].value('Relationship[1]', 'Varchar(255)') AS Relationship,
 [Row].value('Name[1]', 'Varchar(255)') AS NAME,
 [Row].value('Columns[1]/Column[1]/Name[1]', 'Varchar(255)') AS 
 'Dependent Object'
FROM @Xml
CROSS APPLY XmlData.nodes('/ApexSQLClean/References/Object/Reference') AS 
 [Table]([Row])

If a SQL script like this is placed in the last position on the script assembly, after executing the deployment SQL script and all custom SQL scripts previously added, an email will be sent containing confirmation that the deployment is complete.

Basically, there’s no limit to the number of added scripts, simply by using the Add script button it is possible to add as many SQL scripts as necessary, and assemble a SQL script list. Furthermore, as the SQL scripts are added, they can also be removed with the Remove script command if some of them are not really needed.

When adding a large number of SQL scripts, it is easy to make a mistake and accidentally attempt to add the same script twice, but ApexSQL Build’s engine won’t allow this. It will recognize a double entry and display a warning message:

While using a batch of SQL scripts for post-deployment, the important thing is the script order, as they will be executed from first to last respectively. Having properly ordered scripts is often crucial for successful deployment so once the optional SQL scripts are added to the list, it is easy to reorganize them by using the Move down and Move up options.

Using this feature, the deployment process can be controlled with ApexSQL Build allowing augmentation and customization of the deployment process with additional custom post-deployment SQL scripts, whether to build a new SQL database or update an existing one.