By: Ashish Kumar Mehta | Updated: 2011-09-26 | Comments (11) | Related: > SQL Server Management Studio
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.
2. In Generate and Publish Scripts wizard screen; click Next to continue with the wizard.
3. In the Choose Objects wizard screen; Select "Script entire database and all database objects" option and click Next to continue with the wizard.
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.
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
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.
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.
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
10. You will be able to see the below snippet once the schema and data is successfully loaded to the destination database.
Next Steps
- Next time you have a need to generate scripts with your data, consider the steps in this tip in addition to Integration Services as well as Backup and Restore.
- Read these related tips:
About the author
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2011-09-26
