245

I'm looking for a way to do something analogous to the MySQL dump from SQL Server. I need to be able to pick the tables and export the schema and the 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 turn around and go back into SQL Server so it needs to maintain GUIDs/uniqueidentifiers and other column types.

Does anyone know of a good tool for this?

Allan Pereira
  • 2,572
  • 4
  • 21
  • 28
Jared
  • 7,165
  • 6
  • 49
  • 52

8 Answers8

386

From the SQL Server Management Studio you can right click on your database and select:

Tasks -> Generate Scripts

Then simply proceed through the wizard. Make sure to set 'Script Data' to TRUE when prompted to choose the script options.

SQL Server 2008 R2

alt text

Further reading:

Callum Watkins
  • 2,844
  • 4
  • 29
  • 49
Daniel Vassallo
  • 337,827
  • 72
  • 505
  • 443
  • 2
    Which version of SqlServer Management Studio is this in? 2005 doesn't seem to have that option. – Jared Feb 23 '10 at 19:57
  • @Jared: It does. Check this: http://blogs.msdn.com/robburke/archive/2006/05/30/610803.aspx – Daniel Vassallo Feb 23 '10 at 22:05
  • 4
    it doesn't have the "Script Data" option – Nathan Koop Feb 26 '10 at 17:49
  • 1
    @Jared, see this link http://vyaskn.tripod.com/code.htm#inserts – Nathan Koop Feb 26 '10 at 17:50
  • How can I ignore producing GO statements after end of each insert statements? Is there any settings? – Shuvra Nov 10 '16 at 11:36
  • I think you mean "prevent" not "ignore" – JoelFan Jan 13 '17 at 18:53
  • In the current SQL Server Management Studio v17.3, the "Generate Scripts..." option has been moved to above of "Tasks" option in the menu after right-clicking on the database. – Chun Lin Sep 16 '18 at 15:45
  • SSMS does make the process for doing something simple somewhat long-winded! It would be nice if Script Table As -> INSERT To had an option to script the actual data rather than just a template for an INSERT statement! Furthermore, why on earth does it generate a script that inserts the records one by one? That seems inefficient, all the GOs aside. – Stewart Mar 18 '20 at 17:47
83

SQL Server Management Studio

This is your best tool for performing this task. You can generate a script that will build whichever tables you wish from a database as well as insert the data in those tables (as far as I know you have to export all of the data in the selected tables however).

To do this follow these steps:

  1. Right-click on your database and select Tasks > Generate Scripts

  2. In the Generate and Publish Scripts wizard, select the "Select specific database objects" option

  3. Expand the "Tables" tree and select all of the tables you wish to export the scheme and data for, then click Next

  4. In the next screen choose how you wish to save the script (the Output Type must remain set as "Save scripts to a specific location"), then click the Advanced button in the top right corner

  5. In the newly opened window, under the General section is a setting called "Types of data to script", set this to "Scheme and data" and click OK

  6. Click Next, review the export summary and click Next again. This will generate the script to your selected destination.

To restore your database, simply create a new database and change the first line of your generated script to USE [Your.New.Database.Name], then execute. Your new database will now have all of the tables and data you selected from the original database.

bluntfakie
  • 993
  • 6
  • 5
44

I had a hell of a time finding this option in SQL Management Studio 2012, but I finally found it. The option is hiding in the Advanced button in the screen below.

I always assumed this contained just assumed advanced options for File generation, since that's what it's next to, but it turns out someone at MS is just really bad at UI design in this case. HTH somebody who comes to this thread like I did.

SQL Management Studio 2012

SilverSideDown
  • 1,162
  • 11
  • 14
  • 7
    It's worth noting the name has also changed to "Types of data to script" ie the last item under "General". – parliament Mar 26 '14 at 17:12
  • In SQL-Server 2014 this option is again somewhere else, at position 4 under general. I have a german IDE, maybe this picture is helpful anyway: http://fs5.directupload.net/images/161202/qya3wcfl.jpg – Tim Schmelter Dec 02 '16 at 11:09
  • @TimSchmelter For others: this option is called "Types of data to script" in english, last option in tab General – Menno Jan 13 '17 at 10:07
24

If you want to script all table rows then Go with Generate Scripts as described by Daniel Vassallo. You can’t go wrong here

Else Use third party tools such as ApexSQL Script or SSMS Toolpack for more advanced scripting that includes some preprocessing, selective scripting and more.

Maisie John
  • 1,016
  • 9
  • 9
8

Check out SSMS Tool Pack. It works in Management Studio 2005 and 2008. There is an option to generate insert statements which I've found helpful moving small amounts of data from one system to another.

With this option you will have to script out the DDL separately.

CTKeane
  • 674
  • 1
  • 9
  • 22
5

SqlPubWiz.exe (for me, it's in C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2>)

Run it with no arguments for a wizard. Give it arguments to run on commandline.

SqlPubWiz.exe script -C "<ConnectionString>" <OutputFile>
Greg
  • 2,163
  • 1
  • 18
  • 40
  • Thanks. I was looking for a command line way of doing this. One thing to note it to specify -targetserver 2008 to get a script for 2008. It's not listed in the help file but it works. – scottheckel Jan 31 '12 at 18:27
2

BCP can dump your data to a file and in SQL Server Management Studio, right click on the table, and select "script table as" then "create to", then "file..." and it will produce a complete table script.

BCP info
https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=319
http://msdn.microsoft.com/en-us/library/aa174646%28SQL.80%29.aspx

Community
  • 1
  • 1
KM.
  • 101,727
  • 34
  • 178
  • 212
2

I know this has been answered already, but I am here to offer a word of warning. We recently received a database from a client that has a cyclical foreign key reference. The SQL Server script generator refuses to generate the data for databases with cyclical references.

Vaelen
  • 191
  • 1
  • 11
  • I guess to work around that you have to step in and temporarily delete a few FK's to allow SQL Server do its job. Or you manually order the INSERT statements for the "offending" tables. That would mean you cannot script all tables as a set but need to go through them a few at a time based on your understanding of the proper order? How have you dealt with such issues? – Eniola Apr 29 '15 at 07:11
  • 1
    To get around the issue of the cyclical references, you need to go in and remove the offending FK Relationship. The only way that I have found so far to do this is to create a Diagram in SQL and chose tables that have a commonality between them - could be a concept such as "User" or "Orders" etc. Then have a look in that diagram for the cyclic reference and remove it by literally selecting it and hitting delete. Saving the Diagram will force a DDL save and the Relationship will disappear. Remember: This is to generate the script, not do the Data insert. Cyclic references do not affect Inserts. – Vaelen May 18 '15 at 04:26