In order to back up information or transfer data from one server to another, it is often required to merge a dump of a database served by any DBMS. Typically, a dump is a sequence of SQL statements to create and populate tables, add constraints, stored procedures, triggers, etc.
Necessary
- - credentials for accessing database servers Microsoft SQL Server, MySQL, PostgreSQL;
- - SQL Server Management Studio;
- - a package of console utilities, including mysqldump and mysqlshow;
- is a console utility package including pg_dump and psql.
Instructions
Step 1
Start generating a dump of the database maintained by the Microsoft SQL Server DBMS. Connect to the server using SQL Server Management Studio. When the application starts, the connection parameters dialog will be displayed. Specify the name and type of the server in it, select the type of authentication. Enter user credentials if required. Click the Connect button
Step 2
In the Object Explorer window, expand the Databases section. Highlight the item corresponding to the target database. Click on it with the right mouse button. In the context menu, select the "Generate Scripts …" item. The wizard window will be displayed
Step 3
Enter options on the Script Wizard pages and click Next. In particular, on the fourth page, select the location where the created dump will be placed (to a file, clipboard or new window). On the fifth page, click Finish. Wait until the process of generating the database dump is completed. It can take a long time
Step 4
Start a shell on Windows or a terminal emulator on Linux-like systems. On Linux, you can also switch to the text console by pressing Ctrl, alt="Image" and one of the function keys F1-F12. This is required to use the MySQL and PostgreSQL console dumpers
Step 5
Check out the help information for the mysqldump utility. Run the command: mysqldump --help Pay particular attention to the options used to specify the target server and user credentials
Step 6
Dump the MySQL database. Run the mysqldump utility from the command line with the required parameters, redirecting its output to a file, or specifying the target file using the -r or --result-file options. For example: mysqldump -p -u myuser -Q mydatabase> /home/myhomedir/tmp/dump.sql In this case, a dump of the mydatabase, including the stored procedure code (option -Q), located on a locally functioning server that can be accessed with the credentials of the user myuser (the password is requested by the utility) will be placed in the file /home/myhomedir/tmp/dump.sql. If the server is located on a different machine, use the -h or --host option
Step 7
Check out the pg_dump utility reference. Run the command: pg_dump --help Note the options -f, -F, -U, -d, -h
Step 8
Dump the PostgreSQL database. Use the pg_dump utility, passing in the required parameters, for example: pg_dump -f /home/myhome/tmp/dump.sql -U postgres template1 This will dump the template1 database, managed by the server running on the local machine. The dump will be placed in the /home/myhome/tmp/dump.sql file. Use the -h option to specify the server address.
