What is sql-dump for?

sql database mysqldump dump
Yang picture Yang · Mar 25, 2010 · Viewed 53.9k times · Source

I know that a SQL dump is a series of insert SQL statements which reflect all the records inside the database. But what is it used for? Why should we dump the database records? Does every database support a dumping function?

Answer

intuited picture intuited · Mar 25, 2010

Somewhat strangely, this is actually the usual way to back up a database. Copying the files themselves that actually hold the data is not the usual backup method, for various complicated reasons.

All relational databases work this way, or at least I've never heard of one that doesn't: they all have a facility to export a bunch of SQL code that, when executed, will recreate the database in the same state it was in when the dump was started.

However these various formats are generally incompatible, due to subtle differences between the various dialects of SQL used by the different database systems. There are utilities that can convert between some of them, but I'm not aware of any 'Rosetta Stone' that handles every possible case.

As well as being the primary method of backing up a database, this technique is also useful when staging the data of db apps between different servers, ie from development to testing to production.

Related questions

Skip certain tables with mysqldump

Is there a way to restrict certain tables from the mysqldump command? For example, I'd use the following syntax to dump only table1 and table2: mysqldump -u username -p database table1 table2 > database.sql But is there a similar …

Read More
Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More