I'm using SQL 2005. I can right click on a database and create scripts for the database that will recreate the structure (tables, views, stored procedures) elsewhere. Or just as a backup, version, etc.
But, is there a way I can schedule it to do this? And output to a folder I choose?
I really appreciate the help.
Don
You could schedule this using SMO probably, though it may take some work to get up and running.
However, a more elegant approach might be to schedule a full backup to a new file (with today's timestamp), and archive it. This way retrieving the scripts is as simple as restoring that version of the database somewhere, and extracting manually.
An even better approach: if you store your change scripts in source control, you should always be able to pull any version of the database.
I've used both SMO's predecessor (SQL-DMO) from VB as well as ApexSQLScript from the command line to do scheduled scripting of objects.
This is fine for very large databases where you do not have ability to quickly restore a database just to look at schema versioning information for small tables/views/procs which happen to live in the same database.
In fact, this is a good argument for separating out small fast-changing schemas into separate databases from large-slowly changing schemas.