Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2008 R2 Generate Scripts Wizard with Database Schema and Data


By:   |   Read Comments (11)   |   Related Tips: More > SQL Server Management Studio

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem

While looking through the new features and improvements in SQL Server 2008 R2, we found a potentially interesting addition within the Generate Scripts Wizard in SQL Server Management Studio. Using the Generate and Publish Scripts wizard you can script out data stored in the tables, which was not allowed in the earlier versions of SQL Server.

Solution

The schema and data option of the Generate Scripts Wizard is recommended when you want to move a smaller amount of data from one server to another. Since the script files are large in size it is recommended to run the generated script file on the destination server using SQLCMD Utility. If you have large data to be moved from one server to another then it is recommended to use Database Backup and Restore Method.

Let us take a look at an example where we script out the entire AdventureWorks database and later execute the generated script which includes table level data using SQLCMD Utility.

1. Connect to SQL Server Instance using SQL Server Management Studio. Then right click on the AdventureWorks database which in this example we want to script out. From the popup menu, select "Tasks" and then "Generate Scripts..." option as shown in the snippet below.


SQL Server Management Studio Generate Scripts Wizard

2. In Generate and Publish Scripts wizard screen; click Next to continue with the wizard.

SQL Server Management Studio Generate Scripts Introdcution

3. In the Choose Objects wizard screen; Select "Script entire database and all database objects" option and click Next to continue with the wizard.

SQL Server Management Studio Generate Scripts Choose Objects

4. In the Set Scripting Options wizard screen; choose the output type as "Save scripts to a specific location" and specify the path where you would like to save the database script file which will be generated by the wizard. Click the Advanced button which is highlighted in the snippet below for specifying the schema and data scripting option.

SQL Server Management Studio Generate Scripts Set Scripting Options

5. In Advanced Scripting Options screen; choose the option Schema and data for the Types of data to script option and click OK to save the changes and to return to Set Scripting Options wizard screen.

a) Data only - If this option is selected, it will only script out data within the tables
b) Schema and data - If this option is selected, it will script out Schema as well as the data within the selected objects
c) Schema only - If this option is selected, it will script out the Schema only

SQL Server Management Studio Generate Scripts Advanced Scripting Options

6. In the Summary wizard screen; you will be able to see a quick summary of all the options which you have selected so far. Click Next to confirm and generate the scripts.

SQL Server Management Studio Generate Scripts Summary

7. In Save or Publish Scripts wizard screen; you will be able to see a Success or Failure message against each object for which the script was requested to be generated. Finally, click Finish to close the wizard.

SQL Server Management Studio Generate Scripts Save or Publish Scripts

8. In case the generated scripts files are too large to open in SQL Server Management Studio, you can utilize the SQLCMD Utility to execute the scripts.

9. In the SQLCMD Utility type the below line and hit enter for the schema and data to get loaded to the destination database.

SQLCMD -S LOCALHOST -d AdventureWorks -i C:\script.sql -E

SQL Server SQLCMD Command

10. You will be able to see the below snippet once the schema and data is successfully loaded to the destination database.

SQL Server SQLCMD Output
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, July 23, 2015 - 1:12:27 PM - Carlos ABS Back To Top

Thanks for the tips, i didn't know we could create Keys with GSW


Wednesday, May 13, 2015 - 12:29:06 AM - Md. Sajidur Rahman Back To Top

Good Article..............Excellent Work............


Saturday, November 22, 2014 - 9:48:44 AM - Mohamed Back To Top

I got an error when choose sql server 2005 in the server version (preparing my DB Error)


Saturday, October 18, 2014 - 2:07:26 AM - Manoj Kalla Back To Top

Thank You, sir,

 

Its very helpful to me.

 

Thanks Again.

 


Friday, April 04, 2014 - 9:04:48 AM - Sonnytwins Back To Top

Is there a way to do this without using the mouse clicking in the GUI?  I'd like to run an SQLCMD command (or some other batch file type) that does this exact same thing (ie, generate the script).

 


Tuesday, December 03, 2013 - 6:06:14 AM - omar Back To Top

thanck you


Wednesday, June 05, 2013 - 2:44:05 AM - Manish Patel Back To Top

Is there any option for Generate Scripts utility in SQL Server Management Studio 2008 R2 using SQLCMD....Thanks in advance!!!


Friday, April 12, 2013 - 3:28:42 AM - rezataslimi Back To Top

Thank you.


Friday, October 07, 2011 - 11:31:14 AM - Jason Back To Top

more ....

It does not have "advance" button, it does not have the same title and look as I described. However, it seems to have the same feature to "script data".


Friday, October 07, 2011 - 11:25:23 AM - Jason Back To Top

I have SQLServer2008R2 10.50.2500.

When I click "Generate Scripts", I got "Script Wizard" -- not same wizard you have, does not have the feature you show.

I can see you have 10.50.1600 that is out of box. Did you do any add-on? How does that "Generate and Publish Script Wizard" come in?


Tuesday, September 27, 2011 - 1:08:23 AM - Rajasekhar Boggarapu Back To Top

 

Good article..i am waiting for this from a long time...:)


Learn more about SQL Server tools