distal-attribute
distal-attribute
distal-attribute
distal-attribute

Full backup of DB

NBS posted 3 years ago in General
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.
kalvaro posted 3 years ago
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.
packzap posted 3 years ago
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. wink

Thanks for any pointers, references, or leads.
ansgar posted 3 years ago
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
packzap posted 3 years ago
"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. unhappy 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!

ansgar posted 3 years ago
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.
Augusto posted 3 years ago
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
ansgar posted 3 years ago
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".
Augusto posted 3 years ago
Thanks Anse, for your fast response. i'll take a look today.
Gracias!
ableur84 posted 4 months ago
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.

fbachofner posted 3 months ago

ansgar wrote: 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.
jfalch posted 3 months ago
"Is it possible to do it with heidiSql ?" no. use mysqldump command line client from a batch file.
ansgar posted 3 months ago

fbachofner wrote: 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.
fbachofner posted 3 months ago
Hi Ansgar:

ansgar wrote: 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)


ansgar wrote: 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!

Please login to leave a reply, or register at first.