Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    Dec 2009
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    "Sandbox" or somehow restore database automatically?

    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.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,089
    Thanks
    23
    Thanked 593 Times in 592 Posts
    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.

  • #3
    New Coder
    Join Date
    Dec 2009
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by sunfighter View Post
    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.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    May 2012
    Location
    USA
    Posts
    102
    Thanks
    0
    Thanked 7 Times in 7 Posts
    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.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,618
    Thanks
    0
    Thanked 645 Times in 635 Posts
    Quote Originally Posted by Old Pedant View Post
    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.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    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:
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •