Scripting SQL 2005 database structure in a nightly job

Question

I'd like to have a job that runs nightly, or even just once a week, that generates a script of our dev databases. They tend to be tinkered with, and developers have a habit of making changes without scripting them, or documenting them.

I'd like to create a job that will essentially mimic what happens when I right-click and do Tasks > Generate Scripts. It would mean that in the event of Something Bad Happening, we're able to rebuild the structure (the content is 'generatable'), and be back up and running without having to restore from backups that may have been lost at the same time as Something Bad Happening.

I've read about sqlpubwiz, but I couldn't find it on the dev machine, only on my local machine, where I've only got the client tools installed. Am I going down the right route?

Answer 1

This posting has a reverse-engineering script I wrote once. It could probably be adapted to do what you want.

Answer 2

Database Publishing Wizard which can be run from the command line.

Answer 3

I'd suggest a different approach that has worked well for me. Run a nightly job that drops the development databases, restores them from a known configuration, and then applies all the change scripts that have been committed to source control.

Advantages of this approach:

• Your change scripts are tested every night
• There are no unscripted database changes
• Developers quickly learn to create change scripts and commit them to source control

When I've taken this approach I've used the latest production backup for the restore source. This introduces uncertainty, because data changes in production can cause unexpected things to happen, but works well if you need to rapidly respond to production issues.

Answer 4

I developed a tool which implements the functionality you are looking for

Answer 5

APEXSQL Script - use the command line version - simply check into version control or whatever