"Sandbox" or somehow restore database automatically?

[shadowsai]

Hello,
How would one restore the database to its original settings automatically, without any user interaction? Thus, all changes made to the database will be "undone", per say, and the original data is restored.

I've seen this done in site/script demos, where you must be able to write data to the database to test it out. However, each night, they would restore the database to its original condition.

[sunfighter]

It's known as a backup and a restore. You can use phpMyAdmin and the tabs on the top of the page. Backup the DB when it is new. Save the generated php for the restore and you should be good to go.

[shadowsai]

Originally Posted by sunfighter

It's known as a backup and a restore. You can use phpMyAdmin and the tabs on the top of the page. Backup the DB when it is new. Save the generated php for the restore and you should be good to go.

Sorry if I didn't make it clear, but I want to restore the database automatically at a certain time of day, without any user interaction.

[Old Pedant]

Yes? So use a CRON job (on Linux) or SCHEDULED TASK (on Windows) to do this.

Note that you will want to shut down the DB while this happens. That is, disallow any connections to it other than by the ROOT user.

You will pardon me if I say that this seems to be a pretty pointless exercise. Why allow any changes, at all, to the database if you are going to reset it each day? Why not simply make all the data READ-ONLY? Then no changes can be made and you'll never need to reset it.

[annaharris]

Log archiving on (ARCHIVELOG mode)—The restore script restores the backed up database files then uses the online and archived redo log files to recover the database to the state it was in before the software or media failure occurred.

[Old Pedant]

Clever, Anna. Not sure it will do what he wants, but would be worth a try.

He would have to pretend that the failure occurred just after he backed up the DB, even if that happens to have been a year or two in the past. Because he wants to always restore the DB to its like-new state.

[felgall]

Originally Posted by Old Pedant

You will pardon me if I say that this seems to be a pretty pointless exercise. Why allow any changes, at all, to the database if you are going to reset it each day? Why not simply make all the data READ-ONLY? Then no changes can be made and you'll never need to reset it.

All the cloud applications I have seen do exactly what the OP is asking for with their demo accounts. You can use their demo accounts to try things out and the database updates actually occur. When you press the restore button or after the specified period the database is restored back the way it was.

Making a demo account read only would be a pointless exercise as then the person wouldn't be able to see the effect of their change request.

[Old Pedant]

Well, fine. But then why use backup/restore?

If you only want to wipe out changes made that day, why not just establish, say, a stored procedure that does so?

Heck, I'd just do something like this:

USE DATABASE startData;
DROP DATABASE activeData;
CREATE DATABASE activeData;
// then for each table in startData:
CREATE TABLE activeData.tableName1 LIKE startData.tableName1;
INSERT INTO activeData.tableName1 SELECT * FROM startData.tableName1;

CREATE TABLE activeData.tableName2 LIKE startData.tableName2;
INSERT INTO activeData.tableName2 SELECT * FROM startData.tableName2;

...

If you have too many tables, you could use the INFORMATION_SCHEMA to loop through all the ones in startData.

But up to maybe 20 tables or so, I'd just use the above code.

Store the SP in the startData database.

This would be much more efficient than a backup/restore based system.