Executing a Long SQL script file in Octopus with Rollback options

murugaboopathy.rathinasamy's Avatar

murugaboopathy.rathinasamy

31 Dec, 2017 10:13 AM

we need to execute a long SQL Scripts (T-SQL commands) with GO statements in octopus. My single SQL script file contains multiple Insert , Update statements with Go statements each, then contains Alter Statements, Executing SP with Parameter values, Dropping Views and User defined functions. While executing such an SQL script file, if any error raises, then entire SQL operations should rollback.

I do not want to use "Red Gate's ReadyRoll-Octopus Deploy" since this have few rollback options but we need to prepare or have down script (I understand that the down script have the script to bring back the database to the previous state). here i have very big database with large scripts to update for each release time. so preparing the rollback script is very difficult and night mare.
Also i do not want to use Direct DB restore options, since my Db have minimum of 25 GB of Data and restoring will take more time. Also i cannot use Transactions in SQL script file since my huge script contains "Go" statements
Please suggest me an best way to perform my operation

  1. 1 Posted by murugaboopathy.... on 31 Dec, 2017 10:16 AM

    murugaboopathy.rathinasamy's Avatar

    Example Script

  2. Support Staff 2 Posted by Reece Walsh on 02 Jan, 2018 11:55 PM

    Reece Walsh's Avatar

    Hi Murugaboopathy,

    Thank you for getting in touch,

    Octopus uses DbUp a software created by Octopus developers for managing SQL Server Database migrations and executing scripts, you can find some additional information at the associated link. This is our recommended way of deploying SQL scripts, and it's very easy to set up if you're familiar with .NET development. We even use this ourselves in Octopus to upgrade the DB on each upgrade. You can refer to the following video for further instruction.

    I've also included links below for some general documentation on Octopus interaction with SQL Server;

    SQL Server Databases

    How To Deploy SQL Server Database

    We also have documentation available on Redgate SQL Deployments located on our blog post.

    Octopus itself doesnt have automated built-in SQL functions, you would need to manually script these as Steps. As you previously highlighted RedGate has built-in rollback functionality by default.

    I was able to find a video discussion on our YouTube Channel relating to Deploying SQL Server Databases using Octopus Deploy.

    Whilst I appreciate that this video is quite lengthy, you may find the information provided useful in evaluating your deploy SQL Deployment process.

    I'd recommend visiting our Community Slack Channel, this channel is driven primarily by the community though Octopus staff do monitor it fairly often to make sure discussions are relevant and on the right track. It's possible another Octopus User could shed their opinion based on your goals/requirements.

    If you require further assistance or if I've misunderstood in any way please do not hesitate to let me know :).

    Kind Regards,

    Reece

Reply to this discussion

Internal reply

Formatting help / Preview (switch to plain text) No formatting (switch to Markdown)

Attaching KB article:

»

Attached Files

You can attach files up to 10MB

If you don't have an account yet, we need to confirm you're human and not a machine trying to post spam.

Keyboard shortcuts

Generic

? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac