2012-09-20

How to export a table as a SQL script using Microsoft SQL Server Management Studio

I come from the PHP-MySQL world, so I am used to being able to quickly generate a backup of a table as a SQL script (CREATE + INSERT) using the easy to find Export function in phpMyAdmin.  When I had to  use SQL Server for a project I was a bit puzzled when I couldn't find a similar feature in Microsoft SQL Server Management Studio (MS-SSMS).  However, a bit of Googling revealed the (cumbersome) solution:

  • In the Object Explorer in MS-SSMS right click the database that has the table you want to export.
  • Select Tasks from the right-click menu.
  • Select Generate Scripts from the sub menu, which will open a pop-up called Generate and Publish Scripts.
  • Click Next on the Introduction screen, which takes you to the Choose Objects screen
  • On the Choose Object screen select the "Select specific database objects" option
  • Expand the Tables object by clicking the little plus sign
  • Select the table(s) you want to export
  • Click Next, which takes you to the Set Scripting Options screen.
  • Choose where you want the output to go (file, clipboard or new query window)
  • Click the Advanced button, which launches a pop-up window called "Advanced Scripting Options" with a list of options
  • Scroll down to the "Types of data to script" option, which is at the very bottom of the set of options called  "General."
  • Change the "Types of data to script" option from "Schema only" to "Schema and data."  This is what will make the SQL include the data from the table.
  •  Click OK to close the Advanced Scripting Options pop-up.
  • Click Next on the Set Scripting Options screen.
  • Click Next on the Summary screen where it says "Review your selections."  This is what triggers the actual export of the table.
  • Click Finish to close the Generate and Publish Scripts pop-up.

There, was that so bad? Well, yes, it was a lot of clicking for something that people probably do relatively frequently, but that is Microsoft for you.