How to use powershell to copy the files and adding them to the project which would be packed using octopack

Question

I would want to do SQL automation operations using cmd or powershell but don't have any idea that how to do that

I am using Octopack which is installed for web project

Consider I have two project

 -- MyWebProject
    -- DbScript
        --DbVersionUpgrades
            --V1
                -- StoredProcs
                -- Insert and Update Queries
                -- Rollback queries
            --V1-Rollback
                -- StoredProcs
                -- Insert and Update Queries
            --V2
                -- StoredProcs
                -- Insert and Update Queries
            --V2-Rollback (Rollback to the V1)
                -- StoredProcs
                -- Insert and Update Queries

• Copy files from DbScript\DbVersionUpgrades to the folder of MyWebProject

• Create a MyWebProject.nuget package which contains the "DbVersionUpgrades" folder as well

What I want to accomplish and can handle myself, goes as follows:

I would want to check for the already deployed version of database in DbVersion table so that I can find out which version is running

2. It would have two columns for upgrade script and downgrade script which would contain text version of "StoredProcs+Insert and Update Queries" so that whenver there's any downgrade or upgrade required I'll execute some instruction for my Global.asax to check the following things

   • Which version is this?
   • Run all the scripts for the version difference between current and the already deployed
   • If It's an downgrade then run rollback scripts available in database, since that package would not be having the sql script which are executed on database

It has to be crafted well and I would publish this steps for all the community users so that they can automate their deployments without third party tool