Stopping SQL from creating databases if there was an error on script

Question

Situation:

When you run a script and there's errors (even if it's just only one), SQL creates the database anyway. You run your script, some errors pop up, yet you see your database was created... but without certain tables, triggers, relations, so on.

It creates only what you "did well" in your script; a completely useless result because you need the whole database, not just what was "done right". Now you have to delete that incompleted database, correct the errors, and hope this time everything goes right. Nope, you got another incomplete, useless database on your hands, and you have to delete it again and check again your script (Rinse and repeat until all errors are solved).

Why SQL does this? Wouldn't it be better to not to create anything at all until the errors are solved from the script? That way I wouldn't have to lose my time deleting lots of databases that don't have everything I need...

Is there a way or an option to "tell" SQL not to create databases unless the script is completely error-free?

I really hope such a solution exists, it's frustating to keep deleting databases. Thank you.

In a nutshell, most rdbms's use something called schema. For example, when you create a table, the software will add that table's metadata to persistent storage with bells and whistles to allow it to also help with your scripts when you have errors, such as trying to stuff a string with 40 characters into a field created to hold 20. When you script out the creation of a database you have to realize these safety checks have not been created yet. — Ross Bush
– Ross Bush, Commented Feb 28, 2015 at 0:17

Code Different · Accepted Answer · 2015-02-28 00:20:05Z

2

Yes, there is a simple solution. It's called transaction management:

SET XACT_ABORT ON
BEGIN TRANSACTION

-- Do your things

COMMIT TRANSACTION

If anything fails, the whole transaction will be rolled back, as if it never existed.

answered Feb 28, 2015 at 0:20

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ross Bush Over a year ago

Also, I would mention that you should look into exception handling. Since the try...raiseerror..catch...finally operations are supported you could wrap your code with another safety net.

Collectives™ on Stack Overflow

Stopping SQL from creating databases if there was an error on script

Situation:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Situation:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related