CMS & WordPress
Anyone know of a module that can take a database snapshot of whatever might be in PRODUCTION at any given time and copy (import) it over into a designated DEVELOPMENT database, either on or off-site? I wouldn't think that something like this would be very difficult to create but have had a hard time finding anything that works. If I could find something like this, I have my own development database to work with without having to constantly do manual exports and imports all the time.
(I'm currently using 6.x)
The command line utility drush can achieve those things and many more.
I thought Drush was more of a development tool... Can you automate a MySQL EXPORT to an off-site database?
DRUSH will work. There's a bit of a learning curve to wrap around the options so that things get written to the right place but it's probably going to be the most efficient way in the long run. If you're just looking for a module to take snapshots of the database at any given time, I would go to the Backup & Migrate module.
I'll check out Drush, but I was originally thinking that I might either just try to create my own module for this or go with something that used that Backup & Migrate in an autonomous way (i.e. - write some script up that could download the backups and import them every so often).
I use Backup & Migrate all the time to clone production sites I'm working on or to move a site from development to production. I also set it up to create a new database snapshot each day via Cron and limit it to a maximum of 7 backups. It will automatically delete the oldest. I could keep any number but I thought 7 was a safe number.
Another more complex (and more complete) way to do this is with Aegir but I don't know enough about it yet to say how. I'm following another discussion elsewhere where a guy is talking about using Aegir to manage all of his sites on a dev server that links to all of the production websites so that they are always in synch and then backing up the whole shooting match to Amazon S3. That's where I want to get to eventually
It's also not that difficult to just do it manually (exporting the production database, and importing it somewhere else), either by mysql command line, or from whatever mysql GUI you might be using. Or just writing a quick PHP script to do it, and then scheduling it as a cron job if you want to make it an on-going thing. Which is probably why you might have a hard time finding something to do this exact specific thing (just not enough of a utility for most people).
Otherwise, Backup & Migrate can be a viable option as others have suggested, for these general types of tasks. If it were me, I would just write up a PHP script to connect to the database, dump the database to a mysql file, then connect to the other database and import. Then schedule it as a cron job. If you search around the interwebs, there's probably code already written for this on stackoverflow, phpclasses or php.net.
Backup and migrate module in Drupal is easier to backup the database. This will do in a web interface. It dumps DB as SQL. It is used only to take backup of the DB and not the site. This module is helpful for Drupal developers to Backup the DB alone.