Problem
I’m looking for a means to accomplish something similar to SQL Server’s MySQL dump. I require the ability to select tables and export the schema and data (or I can export the schema via SQL Server Management Studio and export the data separately somehow).
I need this data to be able to return to SQL Server, thus GUIDs/uniqueidentifiers and other column types must be preserved.
Is there a good tool for this that anyone knows of?
Asked by Jared
Solution #1
You can right-click on your database in SQL Server Management Studio and choose:
Tasks -> Generate Scripts
Then simply follow the wizard’s instructions. When requested to select script settings, make sure ‘Script Data’ is set to TRUE.
Further reading:
Answered by Daniel Vassallo
Solution #2
This is your most effective tool for completing this task. You can create a script that will create any tables you want from a database and then insert data into those tables (as far as I know you have to export all of the data in the selected tables however).
Follow these steps to accomplish this:
Simply create a new database and alter the first line of your created script to USE [Your.New.Database.Name], then run the script. All of the tables and data from the original database will be present in your new database.
Answered by bluntfakie
Solution #3
It took me a long time to find this option in SQL Management Studio 2012, but I did. In the screen below, the option is hidden in the Advanced button.
Because it’s adjacent to File generation, I always figured it had only advanced options for File generation, but it turns out that someone at Microsoft is just incredibly awful at UI design in this situation. HTH to anyone who stumbles into this thread in the same way I did.
Answered by SilverSideDown
Solution #4
If you wish to script all of the table rows, use Daniel Vassallo’s Generate Scripts method. You can’t go wrong with this place.
Else For more advanced scripting, like as preprocessing, selective scripting, and more, use third-party tools like ApexSQL Script or SSMS Toolpack.
Answered by Maisie John
Solution #5
Look into the SSMS Tool Pack. It’s compatible with both Management Studio 2005 and 2008. Insert statements can be generated, which I’ve found useful for moving little bits of data from one system to another.
You’ll have to write out the DDL separately if you choose this option.
Answered by CTKeane
Post is based on https://stackoverflow.com/questions/2321052/how-to-get-script-of-sql-server-data