Database save, restore and copy

An application runs training sessions. Environment for each session (like "mission" or "level" in games) is stored in a database.

Before starting a session, user can choose which of many available databases to use.
During the session database may be modified.
After the session changed database is usually discarded, but sometimes may be saved under new or same name.
Databases are often copied between non-connected computers (on a flash card).

If environment were stored in plain files, it would be easy: copy, load, save. We currently use similar approach: store databases as MS SQL backups, copy and save them as files, and load into actual DBMS when session starts. Main problem is modification: when database schema changes, all the backups must be updated, which is error-prone.

Storing everything in a single database with additional "environment id" relationship and providing utilities to load, save and copy environments seems too complex for the task.

What are other possible ways to design for that functionality? This problem is probably not unique and must have some though-out solution.

Firstly, I think you need to dispense with the idea of SQL Backups for this and shift to tables that record data changes. Then you have a source database containing all your regular tables, plus another table that records a list of saved versions of it.

So table X might contain columns TestID, TestDesc, TestDesc2, etc Then you might have a table that contains SavedDBID, SavedDBTitle,etc

Next, for each table X you have a table X_Changes. This has the same columns as table X, but also includes a SavedDBID column. This would be used to record any changed rows between the source database and the Saved one for a given SavedDBID.

When the user logs on, you create a clone of the source database. Then you use the Changes tables to make the clone's tables reflect the saved version. As the user updates the main tables in the clone, the changed rows should also be updated in the clone's Changes tables. If the user decides to save their copy, use the Clone's changes tables to record the differences between the Source and the Clone in the original database, then discard the Clone.

I hope this is understandable. It will certainly make any schema changes easier to immediately reflect in the 'backups' as you'd only have one database schema to change. I think this is much more straightforward than using SQL Backups. As for copying databases around using flash cards, you can give them a copy of the source database but only including info on the sessions they want.

What exactly is the purpose of Changes table in this setup? You already clone the database for session, so you can find all the changes at save time or simply overwrite source with clone. It makes sense to have either Clone or Changes, both is overkill.

ima 2009-08-16 15:08:12

I read it again, and may be there is misunderstanding. Different "Databases" or "environments" are not versions, they are independent. Think about game with 100 levels or maps: you can choose one before playing, you can edit and save it, you can send new map to someone. You answer deals with rolling back or saving changes of one of the "maps" - that's not exactly a problem. I'm asking about storing, loading and distributing that 100 levels.

ima 2009-08-16 15:18:26

I think my explanation was unclear. My method would work fine for one source database with numerous derivatives (albeit considerable up-front work). If you had multiple source databases with unrelated schema, then each should follow this pattern of Source/"Dynamic Clones".It's the schema change causing the problem. With levels, or any other files, changes to the structure of one copy aren't automatically propagated through to its derivatives.

CodeByMoonlight 2009-08-16 18:10:52

The description is clear, the reasoning is not. What is the purpose of Changes tables? (You can add ID column to actual tables, as mentioned in question as "environment id relationship"). Do you suggest using original tables as a kind of surrogate view? Why not define actual view - but what view would it be, considering that "working set" view is in Clone?The solution of export-import "Give them a copy... only including" is lacking to say the least. I don't even want to start describing how complicated and plain wrong it gets in real world implementation.

ima 2009-08-16 19:15:07

What advantages does it have over having multiple databases on single server?

ima 2009-08-16 15:10:18

e.g. much easier and more flexible to roll ANY changes back (not limited to changes in the database only)e.g. they may decide to install a SQL patch or they may encounter a corruption in a database. it is much easier to revert back to a snapshop than trying to restore databases, deinstalling patches etc

DmitryK 2009-08-17 00:37:26

I don't think it has anything to do with the question.

ima 2009-08-17 04:26:22

Why??? You have your session start when users expect to find environment in a clean condition. Then users do some changes to the environement. Then we want (usually - according to the author) to discard all changes made (this what revert to snapshot is for).If we want to keep these changes then we can either clone this VM or (if it is a global change) commit changes and have the snapshop updated.The author asked about other ways of doing what he is already doing and I suggested virtualisation. What's wrong with it???

DmitryK 2009-08-17 06:40:23

Just to be clear - one part of our business runs training classes and they do these tricks with VMs all the time. Saves time.

DmitryK 2009-08-17 06:41:20

Running training classes with clean environment is completely different to the situation described in the question.

ima 2009-08-17 16:13:32

ansaurus

tags:

views:

answers:

Database save, restore and copy

related questions