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. http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx mysqldump.html Command%20Reference%20Guide/copydbcmd.htm sqlite.html Basic invocation bcp database out file options mysqldump options database pg_dump options database copydb dbname options sqlite3 dbfile .dump msqldump -d database Authentication to target server -U username -P password -S host -uusername -ppassword -hhost -Pport -Uusername -Wpassword -uusername -P -uusername Save dump into file in basic invocation -rfile / --result-file=file -ffile / --file=file .output file.once file redirect msqldump output Dump only schema -d / --no-data -s / --schema-only .schema msqldump -d "database" -D Add DROP schema at start -c -add_drop Prevent dumping of access privileges -x / --no-privileges / --no-acl Dump only data default -n -t / --no-create-db --no-create-info -s / --data-only -t tablename Dump data as tab-separated default --tab=path / -Tpath .mode tabs Dump data as INSERT statements default -d .mode insert default Dump everything as XML file --xml / -X Ordering of data -h "ORDER (column, ...)" --order-by-primary |} Restoring database Command-line SQL query http://msdn2.microsoft.com/en-us/library/Aa214007(SQL.80).aspx mysql.html app-psql.html Command%20Reference%20Guide/sqlcmd.htm http://www.sqlite.org/sqlite.html Basic invocation sqlcmd options mysql options [database] psql options [database] sql options database sqlite3 filename options mclient -d database Authentication to target server -S host -d database -U username -P password -uusername -ppassword -hhost -Pport -Uusername -Wpassword -hhost -pport -uusername -Ppassword -Ggroup -Rrole Execute query from command line and exit -Q query -e query -c query query -sstatement Execute query from command line and continue with interactive prompt -q query -i Input fileCan be also done using shell < redirection -i file -f file -init file file Output to file instead of stdoutCan be also done using shell > redirection -o file -o file Copy output also to file --tee=file -L file |} References