Script to generate database maintenance script?

[TomLeMes]

Hi, I would like to create a script that I can run against any database (call it the *source* database). What it would do is look through all tables and Stored procedures and generate a new script (let's call it DBMaint) that would re-create the *source* database completely. However, I'd like to be able to run DBMaint against an existing *destination* database which may already contain some or all of the tables and or fields (or be completely empty). The logic for each table (and each field within each table) would be:

If Table doesn't exist then create table
If Field doesn't exist then create field
If Field [b][i]does[/i][/b] exist then check it is of correct size/attributes - if not then change size/attributes accordingly
Delete any fields or tables in the *destination* database that weren't in the *source* database

The idea being that I can setup my dev database correctly with new fields or with some fields removed etc. for the next release of my application (this would be the *source* database), then I can run the main script against the *source* database to generate the DBMaint script. Then this can be run against the live database (this would be the *destination* database) at the time that the application version is released to live. Hope this makes sense!

My main question is: Before I start work on this, is there something out there that does this already? Thanks,

Tom

 

[jrbarnett]

For building a test or development system, I just take the latest backup of live and restore it over the existing test/dev database, then change the SQL account permissions and replace any confidential data (eg salaries, personnel numbers) with dummy values.

If it links into an existing application I also change any settings that need to be done to make it say "Test System" or "Development System" as appropriate.

John

 

[TomLeMes]

Hi John,

Thanks for your suggestion. The issue that I'm dealing with here is that the database structures of the live system are usually (and intentionally) different from the dev system. In my dev environment I may have some new fields, say for a new feature that I've recently added and have been testing. Similarly, I may have removed some fields that are no longer used/required.

Once I am ready to release the new version of my application into the live environment, I will need the live database to have the same field structure as my dev database (e.g. it will need the new fields to be added and it will need the superfluous ones removed). However, obviously I don't want to wipe out the existing live data!

So rather than taking the structure of the live database over to the dev database, what I'm actually trying to do is take the structure of the dev database over to the live environment. And in fact I have several different environments - one for development, one for testing, one for live. Each version of the application will move first to testing, then to live. My generated script would go with the version it belongs to, so that it can be run on any database at the point of roll-out. I hope this makes things a bit clearer. Thanks,

Tom

 

[SQLSister]

Look at Red-Gate software's SQl Compare. I think it will do what you want.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[TomLeMes]

Thanks SQLSister, I actually have Red Gate's SQL Compare and definately agree it's really handy. But my understanding of it is that you need to first use it to compare two specific databases, then use the synchronize script to update one database to have the same structures as the other.

I'm looking to create a generic script that can be run on any database. It checks for the existence of tables and then fields within the tables. At any point, if either a table or a field within table is missing then it creates them. So in theory this script could run on an empty database, or on one that is only marginally different.

Anyway, thanks for the input everyone. I just wanted to check before I got going on this that I wasn't going to spend time re-inventing the wheel.

Tom

 

[SQLSister]

I see what you are saying now. You don't want to know the two databases in advance, but make a script that would run against any second database without modyfying the script? I'm not aware of anything like that.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[jrbarnett]

When I worked with Sybase, there was a program called dbtran supplied with it that would take a transaction log file as input and turn it back into SQL statements.

I'm sure that there must be a way of doing something like that with other databases, all you would need would be to extract the CREATE, ALTER and DROP statements, plus any UPDATE statements to fix version numbers in system parameter tables.

John