Designing Database Changes Before Deployment: Level 1 of the Stairway to Reliable Database Deployments

Question

Post reply

Designing Database Changes Before Deployment: Level 1 of the Stairway to Reliable Database Deployments

Massimo Preitano

SSC Veteran

Points: 206
More actions
April 15, 2026 at 12:00 am

#4755663

Comments posted to this topic are about the item Designing Database Changes Before Deployment: Level 1 of the Stairway to Reliable Database Deployments

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80389 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Bogdan Old Hand Points: 373 More actions · Answer 2

Bogdan

Old Hand

Points: 373

April 18, 2026 at 2:33 pm

#4772590

IT'S Ok

Bogdan Old Hand Points: 373 More actions · Answer 3

I teeling you litttle secret:

SET XACT_ABORT ON

BEGIN TRANSACTION <-- THIS CREATE A TRANSACTION

etc ETC TSQL Scripts

Is simple , much simple

This reply was modified 2 weeks, 2 days ago by Bogdan.

Massimo Preitano SSC Veteran Points: 206 More actions · Answer 4

Hi Bogdan, thanks for your comment.

I totally agree with you — sometimes we tend to introduce unnecessary complexity where a simpler approach would do the job.

That said, part of the confusion here may come from the term rollback, which can be a bit misleading.

In everyday usage, a rollback is usually seen as something that gets triggered when something goes wrong, simply undoing the changes to prevent further issues.

In the context of this Stairway, however, rollback is meant in a broader sense: as a way to reconstruct a known baseline, regardless of the situation — whether a script fails, completes successfully, or is not executed at all.

In that sense, a rollback is less about reacting to errors and more about ensuring that we can always return to a consistent starting point before applying a changeset.

Bogdan Old Hand Points: 373 More actions · Answer 5

IT'S OK

Simplest method is restore:

CREATE DATABASE SNASPHOT

EXECUTE Migration TSQL Scripts
-

IF ERROR 

RESTORE FROM SNASPHOT DB

This reply was modified 1 weeks, 4 days ago by Bogdan.
This reply was modified 1 weeks, 4 days ago by Bogdan.

Massimo Preitano SSC Veteran Points: 206 More actions · Answer 6

Hi Bogdan, that can certainly work in some scenarios.

One important difference, though, is that restoring from a snapshot affects both data and metadata. If you restore a snapshot from, say, two days ago, you also lose any data changes made during those two days.

The approach described in the Stairway is focused on managing schema evolution (metadata), allowing you to move backward and forward between structural states while keeping the data intact.

In other words, it’s not only about recovering from a failed deployment, but about being able to reconstruct a given structural baseline without impacting the underlying data.

Anyway, these topics are covered more in depth in the upcoming steps of the Stairway — feel free to stay tuned, more is to come.