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

Full backup of DB

User, date Message
Written by NBS
2 years ago
Category: General
1 posts since Thu, 02 Feb 12
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.
Written by kalvaro
2 years ago
587 posts since Thu, 29 Nov 07
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.
Written by packzap
2 years ago
2 posts since Tue, 23 Oct 12
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.
Written by ansgar
2 years ago
4936 posts since Fri, 07 Apr 06
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
Written by packzap
2 years ago
2 posts since Tue, 23 Oct 12
"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!
Written by ansgar
2 years ago
4936 posts since Fri, 07 Apr 06
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.
Written by Augusto
2 years ago
3 posts since Fri, 30 Nov 12
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
Written by ansgar
2 years ago
4936 posts since Fri, 07 Apr 06
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".
Written by Augusto
2 years ago
3 posts since Fri, 30 Nov 12
Thanks Anse, for your fast response. i'll take a look today.
Gracias!
 

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