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.
