Hello,

Our elevatedb server can have 200 or more databases. This elevetedb server has users en roles defined so that we can configure which user has access to which database.

Our question is what is the best way to backup this?

Backup all 200 databases. But what about the roles and the users do we have to run a special backup command for this. Because this is the configuration database?

Is it best to stop the elevatedb service and copy the complete elevatedb directory? We don't want to do this because our application runs 24/24 and 7/7.

Or is there an other way to do this?

Thanks.

Regards,
Jeffrey

Jeffrey,

You can write a script to backup all the databases, or even better, a JOB that will back them up automatically. For Roles and Users, they are all in the configuration file (EDBConfig.EDBCfg) -  you only have to backup this file, not the entire folder. Also, EDB automatically creates an "EDBConfig.EDBCfg.Old" file containing a copy of the old configuration data every time something changes in the configuration, like adding a new user or role.

--
Fernando Dias
[Team Elevate]

Accowin


For a 24/7 operation it gets a bit difficult. But thinking a bit weirdly what about using ElevateDB's replication capabilities for the databases involved. You'd need to set up a second copy of the databases initially. The copy can then be backed up whenever you want without disturbing the live databases. Others who use replication can probably advise wether this is feasible or not.

For the configuration file it depends on how you update that. If you can capture the sql used in adding new users / altering roles etc then that can also be passed over to the copy keeping that in sync. If not then a live copy may capture everything (its a fairly small file).

Roy Lambert

Thanks for the information all and the fast reply.

We are already checking how we can replicate the online server to a second place so that we always have a backup system up and running if somethings goes wrong with the production server. We have to indeed check what is the best way to replicate the add/remove of users and roles.

Nice hint to know that we can easily copy the configuration file to backup the defined users and roles.

Thanks.

Regards,
Jeffrey

Jeffrey,

Replication is a good backup alternative, but you can't replicate the configuration file, where the users and roles are, you still have to backup the configuration file somehow.

--
Fernando Dias
[Team Elevate]

Jeffrey,

<< Our question is what is the best way to backup this? >>

My recommendation would be to copy the contents of these tables to tables
with the same structure in a special "System" database, and then include
them in a special backup:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Users_Table
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Roles_Table
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=UserRoles_Table
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Databases_Table
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=DatabasePrivileges_Table
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Jobs_Table
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Stores_Table
http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=StorePrivileges_Table

Except for the privileges table, each of these tables has the CREATE SQL
included, so it's very easy to loop through the table and recreate the
objects, if needed.  Hopefully, at some point I'll have this a bit more
automated.  But, for now you can copy those tables to another database by
executing something like this from the target database:

CREATE TABLE Users AS SELECT * FROM Configuration.Users WITH DATA

CREATE TABLE Roles AS SELECT * FROM Configuration.Roles WITH DATA

etc.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

excellent solution, thank you.

Uli

Tim

>My recommendation would be to copy the contents of these tables to tables
>with the same structure in a special "System" database, and then include
>them in a special backup:

Nice - I wondered about something like this but concluded that since it was stuff in the configuration database it wouldn't work.

What's the odds on the configuration file getting updated if a simple Windows copy is carried out?

Roy Lambert

Roy,

<< What's the odds on the configuration file getting updated if a simple
Windows copy is carried out? >>

That's a tough call to make, which is why I usually advise against straight
copies of system-critical files like that.  It's possible to get a corrupted
backup file as a result, and just the fact that it's possible is enough to
warrant avoiding them.

Tim Young
Elevate Software
www.elevatesoft.com