Best method for backing up DB?

Lawrence · - Joined: 16 Jan 2003 Posts: 207 Location: Brisbane, AU

Can anyone suggest a good way to back up the database? I'm looking to set up a script to trigger a backup to another machine in the middle of the night, but... I can't just copy the DB files, as they're locked while in use.

What else can I do? Should I try and stop the SQL server while the copying's taking place? Is there an SQL command to back the data up to a new location?

Also, if I have to shut the SQL server down, is there a way to automate an Abyss shutdown or redirect to a 'now performing maintenance' page?

I look forward to any suggestions.

olly86 · - Joined: 25 Apr 2003 Posts: 993 Location: Wiltshire, UK

If you are using MySQL, you can use the MySQL Administrator to schedule a backup.
_________________
Olly

AbyssUnderground · - Joined: 31 Dec 2004 Posts: 3855

I use a program called Delta Copy which ignores the locked files problem and copies it anyway.

Norton Ghost will also do this, but costs. Delta Copy is free.
_________________
Andy (AbyssUnderground) (previously The Inquisitor)
www.abyssunderground.co.uk

hyperial · - Joined: 22 Aug 2007 Posts: 23

I wrote a script that does database backup as well as backing up the web files, then it FTPs (using CURL) it up to a remote server every night at 11:50pm. It tars and gzips the files before uploading them.

The script is written in BASH shell on Linux... I have modified it a little to work using cygwin on a Windows box as well as writing a similar DOS batch file using the CURL and ZIP commandline ports.

Here is the linux version I blogged on last year...

http://www.idude.org/2007/04/07/automated-backup-dated-bash-script-with-cron-execution/

aprelium · - Joined: 22 Mar 2002 Posts: 6800