This article will show how to export a SQL Server database as a script, similar to what the MySQLDump program offers for MySQL.
NOTE: This method of exporting a SQL Server database is more error-prone than the standard backup/restore model that SQL Server supports. However, this method allows you to export and import(by running the generated scripts) from a remote machine with no filesystem access on the source or destination server.
-
Open SQL Server Management Studio.
-
Expand Databases.
-
Right-click on the database you wish to export and choose Tasks -> Generate Scripts…
-
Click Next to move past the Introduction.
-
On the Choose Objects page, you can choose whether to export the entire database, which is the default, or to choose specific objects (Tables, etc.) to export.
-
Click Next after making your choice.
-
On the Set Scripting Options page, you will choose where to save the exported SQL script.
NOTE: If you are exporting a large database, it is recommended to use the Save to file option.
-
Click the Advanced button, which will bring up the Advanced Scripting Options window.
-
Change Types of data to script from Schema to Schema and data. This is an important step. If left undone, you will only export the table structure without the actual data.
-
Enable anything else that you want exported, such as triggers, full-text indexes, collation, etc. and click OK.
-
Click Next.
-
Verify the settings are correct on the Summary page.
-
Click Next to start the export.
-
The last page will show the progress as the export runs and any errors will be displayed.
