Full backup of DB

NBS posted 3 years ago in General
HI, i m using 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 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 2 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}
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 2 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:
packzap posted 2 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 2 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 2 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 2 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 2 years ago
Thanks Anse, for your fast response. i'll take a look today.
ableur84 posted 2 weeks 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.

thomosgee1 posted 2 weeks ago
Nice new features. I did find a bug with the local number format; if a number is formatted (3968872 -> 3.968.872) and you right click on it to filter on it, HeidiSQl uses the formatted number in the filter instead of the 'real' value.


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