SQL Dialects Reference/Administration

While this topic is not directly about SQL language, it is sufficiently close to gather information about frequently undertaken administrative tasks together.

Dumping database

Dumping the database involves creation of a file that stores all the data (rows) and the schema for this data from the desired databases/tables. Most usually it's done as a series of SQL statements, combining CREATE DATABASE / CREATE TABLE statements to recreate the schema and INSERT statements to refill the rows — thus, usually the resulting file can be directly imported using command-line SQL client by simple execution of queries. However, sometimes other output formats are desired and some databases (at least MSSQL) lack the ability to serialize the database as SQL statements from command-line dumper client.

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version	Feature	Standard SQL:2011	DB2	Firebird	Ingres	Linter	MSSQL	MySQL	MonetDB	Oracle Vers. 11.x	PostgreSQL	SQLite	Virtuoso
?	Basic invocation	?	?	?	copydb dbname options	?	bcp database out file options	mysqldump options database	msqldump -d database	?	pg_dump options database	sqlite3 dbfile .dump	?
?	Authentication to target server	?	?	?	-uusername -P	?	-U username -P password -S host	-uusername -ppassword -hhost -Pport	-uusername	?	-Uusername -Wpassword	N/A	?
?	Save dump into file	?	?	?	?	?	in basic invocation	-rfile / --result-file=file	redirect msqldump output	?	-ffile / --file=file	`.output` file `.once` file	?
?	Dump only schema	?	?	?	?	?	N/A	-d / --no-data	msqldump -d "database" -D	?	-s / --schema-only	`.schema`	?
?	Add DROP schema at start	?	?	?	-add_drop	?	N/A		N/A	?	-c	N/A	?
?	Prevent dumping of access privileges	?	?	?	?	?	N/A	N/A	N/A	?	-x / --no-privileges / --no-acl	N/A	?
?	Dump only data	?	?	?	?	?	default	-n -t / --no-create-db --no-create-info	-t tablename	?	-s / --data-only	N/A	?
?	Dump data as tab-separated	?	?	?	?	?	default	--tab=path / -Tpath	?	?	?	`.mode tabs`	?
?	Dump data as INSERT statements	?	?	?	?	?	N/A	default	default	?	-d	`.mode insert`	?
?	Dump everything as XML file	?	?	?	?	?	N/A	--xml / -X	N/A	?	N/A	N/A	?
?	Ordering of data	?	?	?	?	?	-h "ORDER (column, ...)"	--order-by-primary	?	?	?	?	?

Restoring database

Command-line SQL query

This page includes comparison tables which can be big and complex.

While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.

SQL version	Feature	Standard SQL:2011	DB2	Firebird	Ingres	Linter	MSSQL	MySQL	MonetDB	Oracle Vers. 11.x	PostgreSQL	SQLite	Virtuoso
?	Basic invocation	?	?	?	sql options database	?	sqlcmd options	mysql options [database]	mclient -d database	?	psql options [database]	sqlite3 filename options	?
?	Authentication to target server	?	?	?	-uusername -Ppassword -Ggroup -Rrole	?	-S host -d database -U username -P password	-uusername -ppassword -hhost -Pport	?	?	-Uusername -Wpassword -hhost -pport	N/A	?
?	Execute query from command line and exit	?	?	?	N/A	?	-Q query	-e query	-sstatement	?	-c query	query	?
?	Execute query from command line and continue with interactive prompt	?	?	?	N/A	?	-q query	N/A	-i	?	N/A	N/A	?
?	Input file^[1]	?	?	?	N/A	?	-i file	N/A	file	?	-f file	-init file	?
?	Output to file instead of stdout^[2]	?	?	?	N/A	?	-o file	N/A	?	?	-o file	N/A	?
?	Copy output also to file	?	?	?	N/A	?	N/A	--tee=file	?	?	-L file	N/A	?

References

↑ Can be also done using shell < redirection
↑ Can be also done using shell > redirection

[1] Can be also done using shell < redirection

[2] Can be also done using shell > redirection

[1]

[2]