Rake task to backup and restore database

Question

I am working on a Rails project, and sometimes I program at home and sometimes at work. In my development process, I add data to the database, and I really need a way to synchronize the databases at home and work.

I am thinking about a Rake task to backup/restore the whole database in a Rails app.
Is there anyway to do that?

Solution 1

write a rake task:

namespace :db do
  task :backup do
    system "mysqldump --opt --user=root --password rose userdetails> xyz.sql"
  end

  task :restore do
    system "mysqldump --user=root --password  < xyz.sql"
  end
end

By the rake db:backup you will get the sql that you can commit to your git/svn and once you work from home to restore pull it and run rake db:restore

Solution 2

I use a script that dumps the the database to a particular location, and a second that fetches the dump and uses it to restore a specified database. I use the Whenever gem to schedule daily backups (by calling the first script), by putting this in the schedule.rb file:

  every :day, :at => "05:00" do
    command "/var/www/current/script/db_backup.sh -n #{@db_name}"
  end

The exact contents of the script depends on what database you're using. As I'm using postgreSQL, the backup script, after figuring the proper location for the dump, runs pg_dump:

pg_dump -F t -U username -f file_location<timestamp>.dat database_name

And the 'restore' script, which I use to copy the production backup to a local database for testing, uses pg_restore:

pg_restore -U username -O -x -d database_name_new path/to/file

If you're using some other database, these tools would obviously be different, but most databases support backup and restoration in some form.