0

I have quite old application with current database (on MSSQL but it does not matter). I scripted it completely with all required static data. Now I want to introduce DB change only via update scripts. So each function, each SP will be placed in stand-alone file and all schema update scripts will be stored in files named like 'SomeProduct01_0001' what means that this script belongs to product SomeProduct, sprint 1 and it is first schema update script.

I know that each script must be absolutely re-runnable, but anyway I want to have functionality to combine these scripts into one based on DB version (stored in DB table).

  1. What common best practices there is to handle bunches of update scripts?
  2. What is better - implement version anylyzis in collector (bat or exe file) or add some SQL header to each file? From other point of view I am already have version - it will consist of sprint identifier and script identifier, not sure that it is ok to duplicate this information in script header.
  3. How to skip file content if user tries to apply it to newer database but keep availability combine this script with any other to perform updates of other old database?
  4. How to avoid database conflicts if combined scripts operates columns/table which still does not exists in database but will be created byt this script (for example, in line 10 table created and in line 60 it is used in trigger or constraint, as I know script will not be validated)? Maybe wrap in EXEC('') entire script? What I need to escape besides sigle quote characters?

UPD: As David Tanzer asnwered it is better to use ready solutions for DB migrations, so it may be best solution for cases like mine. It was not and answer exactly for my question, but it is suitable for new solutions.

Improve this question
2
  • Check out tools like Liquibase or Flyway Commented Apr 15, 2014 at 9:33
  • @a_horse_with_no_name thanks, very interesting migration tools. As I understood it is oriented mostly on development environment. I could not find any mention about creationg delivery package using these tools. Commented Apr 15, 2014 at 9:38

1 Answer 1

Reset to default
1

You don't have to implement this yourself, there are tools that do it. Take a look at dbmaintain, it provides almost exactly the functionality you described:

http://www.dbmaintain.org/overview.html

I know of and have worked with several teams who use it to manage their database schemas in all their environments: Development, Testing, Staging and Production.

http://flywaydb.org/ seems to be another tool to do this, and it has even more features. They even have a comparison of multiple tools on their homepage (including dbmaintain)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@AlexG.P. Could you also accept the answer if it has helped you? Thanks!
It is not exactly what I've asked about, but it is suitable for my case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.