Database copy

Databases
1

Hello,

I’m wondering about an efficient way to copy a live database into a test database at intervals of 3 months.

Currently, I’m manually deleting the test database and copying each table of the live database into a new test database by mean of a PHP script.

Is it possible to schedule a job to take care of this task for instance?

Thanks in advance.

2

If you’re using a linux box then you should have cron jobs available to you

3

In conjunction with using mysqldump and the mysql commands.

4

assuming it’s a mysql database, of course

5

Of course, and if it is SQL Server, and you are running 2005 or higher, you can write an SSIS task that will perform the task for you and set it up using a Job so it runs every 3 months.

6

Is there any alternatives to mysqldump? It seems to be a bit slow.

7

When you’re copying across the data manually are you using a select insert query?

8

Have you tried looking at the flags you can set? Can you show us the command you were running (without the username and password, of course)

9

I made a simple test with the standard command.
mysqldump -u username -p password db_name > dump.sql

The dump file is stored in the same I/O drive which might be causing extra delay.
The only option I have is to compress the output.