Automated script generation
Posted on 2008-06-25
Bit of a conundrum here. SQL server management studio has a generate scripts function that allows you to script any operation that it performs, except one. It doesn't offer the ability to script the script generation function.
We run an in-house developed reporting system that downloads its static data overnight and its dynamic data from the live server on an ad-hoc basis. Due to the fact that the data is refreshed daily the data itself does not require backing up, however the database structure itself represents quite a large investment of time and resources.
I've been able to capture this structure via the script wizard but have so far been unable to automate the script generation process so that it can be backed up without the multi-gigabyte overhead of the data. I'm looking to do it through integration services if possible?
any help would be much appreciated.