How to get script of SQL Server data

Question

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

User · Answer

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

User · Answer

From the SQL Server Management Studio you can right click on your database and select   Tasks - gt  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    Further reading    Robert Burke  SQL Server 2005 - Scripting your Database

User · Answer

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

User · Answer

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

User · Answer

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   lt ConnectionString gt    lt OutputFile gt

User · Answer

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

User · Answer

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    Right-click on your database and select Tasks   Generate Scripts In the Generate and Publish Scripts wizard  select the  Select specific database objects  option Expand the  Tables  tree and select all of the tables you wish to export the scheme and data for  then click Next 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 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 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

User · Answer

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

[sql] How to get script of SQL Server data?

Examples related to sql

Examples related to sql-server

Examples related to export