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!