1

Suppose I have a live website running on a live database and I have a dev website that is a duplicate of the live website.

The live database is where customer transactions are stored.

Suppose I do some changes on the dev website and dev database like add another column in a table. How can I retain all of the customer transaction inside live but at the same time saving all the changes I did from the test website to the live website?

Improve this question
5
  • 1
    Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer. Also see How to Ask Commented Oct 1, 2018 at 12:34
  • Maybe Liquibase for database versioning allows you to update the database in a safe way. It adds their own table to the database with versioning info, and you have an XML in which to place database changes grouped: addColumn, createIndex etcetera. But try it first. Commented Oct 1, 2018 at 12:42
  • 2
    I think you are looking for the concept of “schema migration”. For example, write a SQL script that performs the schema change and test it. Then, take your production DB down for maintenance, run the script, and go live again. Some ORMs can do schema migrations automatically. Commented Oct 1, 2018 at 12:56
  • You might get better answers if you move your question to dba.stackexchange.com, the people at dba.SE know more about specific database then the software engineers. They will know which changes can be done online and which can't. Commented Oct 1, 2018 at 14:37
  • @amon as said the very creator of hibernate in a twitt, automatic schema migrations are for dev usage only, not for production. So no, keep a dedicated proper migration script, either using liquibase or raw SQL. Commented Oct 3, 2018 at 8:48

2 Answers 2

Reset to default
3

You need to write/generate scripts that have the changes you made in them. For example if you add a few columns to the table the SQL for this might be:

ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;

Depending on the toolsets you have and the databases you use, there are various products that will help automate the generation of code. For example, In the SQL server world you could use Visual Studio database projects or Redgate. Other platforms will have similar tools available.

Improve this answer
1

You need three sets of scripts:

  1. to prepare your database for migration
  2. to perform the migration
  3. to put your database back in a workable state

In preparing your database for migration, it is acceptable to put it in an "unstable" state (for example, drop FK constraints, denormalise tables, backup tables and columns).

The actual migration scripts change your database as you require. Not much else.

The last set of scripts reinstate your database to the desired state. Restore tables and columns, put back constraints, drop old tables and columns, etc.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.