Creating table’s SQL dump in SQL Server Management Studio

Owned by Robert Sikorski
Last updated: 27 Jul 2022 by Wojciech Mozolewski
2 min read

Very often, issues reported by our clients are caused by incorrect/corrupted entries in the tables (which store the data). In such cases we usually ask to export and send us these tables for investigation. It is always possible to quickly export a table by using DataPump’s export script or Earthlight’s Export tool, however these tools allow you to export data to specified formats (shp, tab, csv etc.). Our data exporting engines are very often fixing incorrect values and we are no longer able to reproduce the issue by using provided data. We strongly suggest to create SQL dump of corrupted table because only this method guarantees that the data are exported in exact same format/value as they are stored in your database.

To create SQL dump of desired table please follow these steps.

  1. Run SQL Server Management Studio
  2. Connect to your database server.
  3. Once you are connected you will see a list of all databases (on the left panel). Please find the database on the list which store the table that you want to export.
  4. Now right-click on the database and in context menu please go to Tasks section and select Generate Scripts option.
  5. Now you should see Generate and Publish Scripts tool dialog window. Please click Next button on the initial screen.
  6. This is a Select the databases object to script screen. If you want to export just one table please do the following steps:
    • click Select specific database object option
    • click small + icon next to Tables to expand tables list
    • find desired table on the list and tick the checkbox next to table name.
    • if you are happy with the settings – click Next button
  7. Now you need to specify all settings for the file that you want to generate. Please make sure that your settings are the same as on the image below. Set name and path for the file and click Advanced button.
  8. In Advanced Scripting Options window scroll down and find ‘Types of data to script’ option and set it to ‘Schema and data’. Next set the 'Script Indexes' to 'True'. Now click OK to accept the settings and click Next to continue.
  9. Now you will see a Summary window. There is no need to set/change anything here. You can just check if all options are correct. If you are happy with the settings – click Next button.
  10. The script is now being created. This operation may take a while (depends on the table size). Once it is completed you should see a confirmation screen similar to the one below. Click Finish button to close the dialog window.
  11. Please go to location specified in settings and check if the file is there.
  12. Congratulations! You have just created SQL dump file. You can now send it to our support team (please remember that you can create ZIP archive to reduce the file size).