SQL Dialects Reference/Administration

From Wikibooks, the open-content textbooks collection

Contents 1 Administration 1.1 Dumping database 1.2 Restoring database 1.3 Command-line SQL query

[edit] Administration

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

[edit] 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.

Operation	MSSQL[1]	MySQL[2]	PostgreSQL
Basic invocation	bcp database out file options	mysqldump options database	pg_dump options database
Authentication to target server	-U username -P password -S host	-uusername -ppassword -hhost -Pport	-Uusername -Wpassword
Save dump into file	in basic invocation	-rfile / --result-file=file	-ffile / --file=file
Dump only schema	N/A	-d / --no-data	-s / --schema-only
Add DROP schema at start	N/A		-c
Prevent dumping of access privileges	N/A	N/A	-x / --no-privileges / --no-acl
Dump only data	default	-n -t / --no-create-db --no-create-info	-s / --data-only
Dump data as tab-separated	default	--tab=path / -Tpath
Dump data as INSERT statements	N/A	default	-d
Dump everything as XML file	N/A	--xml / -X	N/A
Ordering of data	-h "ORDER (column, ...)"	--order-by-primary

[edit] Restoring database

[edit] Command-line SQL query

Operation	MSSQL[3]	MySQL[4]	PostgreSQL[5]
Basic invocation	isql options [-d database]	mysql options [database]	psql options [database]
Authentication to target server	-U username -P password -S instance -H host	-uusername -ppassword -hhost -Pport	-Uusername -Wpassword -hhost -pport
Execute query from command line and exit	-Q "query"	-e query	-c query
Execute query from command line and continue with interactive prompt	-q "query"	N/A	N/A
Input file[1]	-i file	N/A	-f file
Output to file instead of stdout[2]	-o file	N/A	-o file
Copy output also to file	N/A	--tee=file	-L file

1. ↑ Can be also done using shell < redirection
2. ↑ Can be also done using shell > redirection