Backup database with phpMyAdmin

[~DS~]

Never trust your host so they say.

I want to make sure the setting is correct as they are there by default?

[Antechinus]

My preference would be to make the following changes:

1/ Check disable foreign key checks.
2/ Check add drop table, etc.
3/ Check add create procedure, etc.
4/ Uncheck extended inserts.

[~DS~]

Thank you, any reason why these changes?

What about "Add into comments
Creation/Update/Check dates" leave it uncheck?

Thanks again.

[Antechinus]

Oh yes you can check that one too if you like. The backup file itself will be dated anyway but there's certainly no harm in having the date in the comments as well. It's not necessary though so suit yourself.

Reasons are:

1/ Someone who knew more than databases than I did told me to do that back when I first asked ages ago. Have never bothered to find out why. I probably should.

2/ You will need this if you check 3/ because otherwise you will get an error when trying to restore and

3/ If you are restoring to an empty db or to one that has damaged tables that cannot be repaired you will want the drop and create queries in your backup. Note that if you want a data-only backup (without the ability to drop and create tables on restoring) you can do do too, which is what your host's default settings are for. I'd rather have the table queries myself just in case I need them. I can remove them if I want to change to to data-only.

4/ Extended inserts checked are a PITA because they put all the info for each table on one line. That makes editing the file (if you ever need to) a real hassle. Imagine a db table that contains thousands of posts all on one line, as opposed to complete inserts which have a new line for each post. Much easier to get your head around if you need to for any reason.

Extended inserts will give a slightly smaller file size but IMO the difference is not worth worrying about unless your db is really huge.