I've got a database change management workflow in place. It's based on SQL scripts (so, it's not a managed code-based solution).
The basic setup looks like this:
Initial/
Generate Initial Schema.sql
Generate Initial Required Data.sql
Generate Initial Test Data.sql
Migration
0001_MigrationScriptForChangeOne.sql
0002_MigrationScriptForChangeTwo.sql
...
The process to spin up a database is to then run all the Initlal scripts, and then run the sequential Migration scripts. A tool takes case of the versioning requirements, etc.
My question is, in this kind of setup, is it useful to also maintain this:
Current/
Stored Procedures/
dbo.MyStoredProcedureCreateScript.sql
...
Tables/
dbo.MyTableCreateScript.sql
...
...
By "this" I mean a directory of scripts (separated by object type) that represents the create scripts for spinning up the current/latest version of the database.
For some reason, I really like the idea, but I can't concretely justify it's need. Am I missing something?
The advantages would be:
For dev and source control, we would have the same object-per-file setup that we're used to
For deployment, we can spin up a new DB instance to the latest version either by running the Initial+Migrate, or by running the scripts from Current/
For dev, we do not need a DB instance running in order to do development. We can do "offline" development on the Current/ folder.
The disadvantages would be:
For each change, we need to update the scripts in the Current/ folder, as well as create a Migration script (in the Migration/ folder)
Thanks in advance for any input!
