Database Build Question

[wilbiew]

As with many software development environments, every software release most likely requires the database to be updated, whether it be creating or dropping objects, etc.

My questions are:

1. We have an idea to drop and re-create many database objects with each release. In a sense, it is almost "refreshing" all of the database objects (stored procedures, views, etc.) even though it has not changed since the last release. The problem is what would we do if an error is generated while deploying the changes to production. The should never happen with extensive testing. However, is there a way to go back to a point in time (before deployment) without a full database restore (which takes too long)?

2. Does anyone know of any good resources on this topic?

[rmiao]

Not sure if you can rollback what you did, backup db before making any change is the key.

[jezemine]

I think that's a strange way to release to production. Most DBAs I know want to see the change script, and want it to be as limited as possible. Most would not be happy with a drop all and recreate all strategy.

In addition to regular backups, you should also have all your scripts in source control. That way it's impossible to lose the code for a sproc unless your source control system goes down...

Here's an app I wrote to help you get your objects in source control (assuming you use msssql here): http://www.elsasoft.org/tools.htm

[skhanal]

If you are using SQL server 2005 you can take a database snapshot before the deployment, if anything goes wrong you can go back to that snapshot.

If you are using Oracle 10g, then you can enable database flashback, and do flashback recovery if deployment fails.

I agree with jezemine that this is not the best method to deploy application changes.