Full backup of DB

HI, i m using 6.0.0.3603 of HeidiSQL. im tring to create backup of my complete DB.
i am trying to execute this query but it says it has an error at line 1.
BACKUP DATABASE `db_npc_opt`
TO 'D:\SQLServerBackups\db_npc_opt.bak'

Can someone tell me how to create complete backup of DB.

I've never heard about the BACKUP DATABASE command, but the fact that you are trying to export to a directory called SQLServerBackups suggests that you might not be using MySQL. But that's weird because you can't get a syntax error unless you are actually connected and HeidiSQL 6.0.0.3603 did not support SQL Server yet.

Never mind...

If you are actually using MySQL, you can do this:

- Find the database in the left tree
- Right click on it and choose "Export database as SQL"
- Review the options in the form and click "Export"

If you are using SQL Server, you need to upgrade HeidiSQL to recent unstable release. However, SQL Server support is still experimental and may work or may not work.

I want to backup my db in the best manner possible. The steps:

1. I select the db on the right side of the HeidiSQL window.
2. Right click and choose "Export db as SQL".

Then a Table Tools window comes up with checkmarks available for "drop" and "create" in the database and table categories. What are the best settings from these to choose?

For example, the Data dropdown box contains these options:
a) No data
b) DELETE + INSERT (trunc . . . {word truncated, I cannot decipher what it fully says :o}
c) INSERT
d) INSERT IGNORE (do not upda . . . {truncated}
e) REPLACE existing data

I would like to get a better idea of what specifically all these options and checkmarks do when backing up the database. As this confuses me just as the internet does with its series of tubes.

Thanks for any pointers, references, or leads.

First of all resize the dialog horizontally so you can read the full drop down items. Probably they make more sense then :)

And then, there are no "best" settings. But I can tell you how I normally do:
* Uncheck both "Drop" checkboxes, no need to do that mostly, and that's critical in some cases.
* Check both "Create" checkboxes, so it creates the structure of tables if they do not exist. That's good :)
* In the Data dropdown, select "INSERT". That's the most normal way of inserting rows into a table.

If you want to understand what you're clicking, make yourself familiar with these keywords:
INSERT, DELETE, TRUNCATE

"Expand the window horizontally". Well duh. I have egg on my face. I've been playing with computers for 25 years and I couldn't even figure that one out! Senility strikes. I guess, I was so confused with the options there, I blanked out on common and simple window manipulations. Geesh.

Thanks for the pointers. I'll select INSERT and the "create" check boxes before saving. I take it that "drop" would actually erase data?

Incidentally, nice program. Thanks for your help!

Yes, "Drop" checkboxes write "DROP TABLE ..." and "DROP DATABASE ..." queries into the dump file. They do not drop anything on the server you are just connected to, but on the server where you reimport the created sql file.

Hi Guys..
A few more questions or better, confirmation of what i'm doing..

Export database will save also relationship between tables?

And what is the right way to "IMPORT" that big file that heidi has created? Heidi support import? or you need to use something like phpmyadmin for example?

Thanks Regards from Uruguay

Foreign keys are supported by MySQL only on InnoDB tables. HeidiSQL will export these if there are any.

Importing works via "Tools" > "Load SQL file". Or, just right click an empty Query tab and click "Load SQL file".

Depending on the size of the file you can easily load too much into HeidiSQL (= memory). So, for large files I recommend using the MySQL command line tool. Which can also be started in HeidiSQL: Tools > "Launch command line".

Thanks Anse, for your fast response. i'll take a look today.
Gracias!

Hi all,

This procedure is usefull and very good:
- Find the database in the left tree
- Right click on it and choose "Export database as SQL"
- Review the options in the form and click "Export"

But my preocupation is for example to make the back-up each day at 08H00 PM

Is it possible to do it with heidiSql ?

Thank for help.

Yes, "Drop" checkboxes write "DROP TABLE ..." and "DROP DATABASE ..." queries into the dump file. They do not drop anything on the server you are just connected to, but on the server where you reimport the created sql file.

It might be useful to reword the dialog "DROP (on target)" (emphasis on proposed added text) to make the operation less ambiguous.

"Is it possible to do it with heidiSql ?" no. use mysqldump command line client from a batch file.

It might be useful to reword the dialog "DROP (on target)" (emphasis on proposed added text) to make the operation less ambiguous.

Yes, quite true. But that not only applies to the DROP checkbox, but also for the others. What about that:
Database(s) on target: [ ] Drop [ ] Create
Table(s) on target: [ ] Drop [ ] Create

The fact that the "Help" button explains that too seems not to be sufficient. The dialog should be intuitive so the user is sure about what he does.

Hi Ansgar:

Yes, quite true. But that not only applies to the DROP checkbox, but also for the others. What about that:
Database(s) on target: [ ] Drop [ ] Create
Table(s) on target: [ ] Drop [ ] Create

Yeah, I was giving you shorthand. ;-)

It would probably be "cleaner" to put it all in a "box," and name the box "On Target", similar to HTML <fieldset><legend> (don't know how Delphi does this)

The fact that the "Help" button explains that too seems not to be sufficient. The dialog should be intuitive so the user is sure about what he does.

Almost no one reads the help file . . .
:(

And Yes, I agree with attempting to build intuitive dialogs!

Did you try to use a dedicated SQL backup software instead of drop-create combinations or HeidiSQL? It can provide some comfort and automation, compared with HeidiSQL manual backup.

Hi Ansgar:

It would probably be "cleaner" to put it all in a "box," and [b]name the box[/b] "On Target", similar to HTML &lt;fieldset&gt;&lt;legend&gt; (don't know how Delphi does this)

Any chance this idea might make it into a near-future version of HeidiSQL?

I just spent some time with exports (in HeidiSQL) today and was saddened to see this has apparently never been addressed.

Without reading the help file I think a new user would be totally confused.

Thanks for your consideration.

Yes, I can change that. Could you please post your suggestion for the change in the bugtracker? Probably with a screenshot.

Hi Ansgar:

Yes, I can change that. Could you please post your suggestion for the change in the bugtracker? Probably with a screenshot.

OK, done.

please see https://github.com/HeidiSQL/HeidiSQL/issues/228

I am also posting the mockup of the proposed enhancement here for those not on GitHub.

Thanks for your consideration!

that would be a nice addition, especially that now the help button is missing.