Learn more about SQL Server tools

mssqltips logo
giveaway
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Generate scripts for SQL Server object migration


By:   |   Read Comments (4)   |   Related Tips: More > Upgrades and Migrations


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem

Our development team created a new module for a production web portal. This new module includes new tables, foreign keys, indexes, lookup tables, associated data, views, stored procedures, user defined functions, etc. which were created in an existing database on one of our development servers.  How can I generate a script to move all the objects for this specific module to our production SQL Server database?  I do not want to lose any related keys, indexes, data, constraints, etc.  Do you have any suggestions on how this can be accomplished?

Solution

SQL Server Management Studio provides an efficient and reliable wizard to generate scripts to transfer objects along with their dependencies as well as the data.  In this example, let's generate a migration script for the following objects from the AdventureWorks database:

Object Type Object Name
Tables [purchasing.shipmethod], [purchasing.vendor], [purchasing.vendorAddress], [production.location]
Views [purchasing.vVendor], [sales.vSalesPerson]
Stored Procedures [dbo.uspGetManagerEmployees]
User Defined Functions [dbo.ufnGetProductListPrice]

One of our requirements is to move the objects along with their related keys, constraints, relations, triggers, user defined data types, etc. To generate a script for the migration process let's right click on the Adventureworks database, select the 'Tasks' and 'Generate Scripts' options as shown below:

object explorer

The first screen in the wizard is the welcome screen as shown below.  Click the Next button to continue the process.  Please note this screen will not appear for those users who have selected the 'Do not show this starting page again.' option.

script wizard

The second screen of wizard is the 'Select Database' screen.  On this screen we need to select the appropriate database.  In our example, this will be the Adventureworks database.  We are also not going to select the 'Script all objects in the selected database' option since we only want to migrate a subset of the objects.  Next click the 'Next' button to continue the wizard.

script wizard database

The 'Choose Script Options' screen provides a comprehensive list of options for the migration script generation. General options are related to script settings and there are also specific options for table/view properties. Select these options very carefully because all these options will be reflected in the generated script for the migration process.

choose script options

Most of the options in the above frame are simple and self explanatory, however below is some additional commentary on these particular options:

Option Description
Convert UDDTs to Base Types Option to convert any user defined data type to its base SQL Server data type or not. This option should be set to true if the objects being transferred contain user defined data types and the user defined data types do no exist in the target database.
Generate Script for Dependent Objects Specifies that any object dependent option will also be included in the creation scripts.
Include Descriptive Headers When this option is set to 'true', comments are added in the script which helps to have a more readable script for troubleshooting purposes.
Include If NOT EXISTS When this option is set to 'true', the script includes IF NOT EXISTS logic.  This logic checks for a pre-existing object of the same name in the schema before creating the object. If the object already exists, then it is not created.  This option should be set to 'true' when there is a chance that the object may already exist in the schema.
Script for Server Version Since the feature set differs between versions of SQL Server, this option will build the script for backward compatibility purposes.
Script Statistics If the script statistics option is set to 'true' then CREATE STATISTICS statements are added in the script for recreation of existing statistics of an object. If script statistics and histogram is selected then histograms will also be included in script. Both statistics and histograms are used by the query optimizer. Both of these should be included when objects are being scripted for troubleshooting of any type on another SQL Server.
Script VarDecimal Options If set to 'true', this option may be used to include in the script the configuration of varDecimal storage format.

After a detailed review of the configuration options, click the 'Next' button to continue with the migration script generation process. On the 'Choose Object Types' screen you will have the option to choose the types of objects that you plan to migrate. In our case we plan to migrate tables, views, stored procedures and user defined functions along with related keys, indexes, constraints, etc as shown below:

choose object types

Once you click the 'Next' button, the 'Output Option' screen for fine tunes the tables, views, stored procedures and user defined function options.  Here you will get screen with generate options for migration script. Below are the sample screen shots for stored procedures, tables, user-defined functions and views:

choose stored procedures

choose tables

choose views

choose user-defined functions

The 'Output Option' screen is intended to determine the final output for the wizard.  The options include generating a file in a specified path, copying the contents to the clipboard or saving the results to a SSMS query window.  In our example, I have choose to script in a new query window.

output option

Next will be the 'Script Wizard Summary'  screen which summarizes all of the options selected in the wizard.  The initial screen will have a top level view of the hierarchy, but you can drill into the view to see the detailed configurations as shown below.  Click the 'Finish' button to begin the script generation.

script wizard summary

Once you press the 'Finish' button the 'Generate Script Progress' screen will outline the status of each step.  In our circumstance a new SSMS window will appear with the final script.  The 'Report' button can also be clicked on to review the detailed status.

generate script progress

Below is a sample screen shot of the script generated in SSMS.  You can also review the generate script here.

ssms script generator
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, October 08, 2013 - 1:07:18 PM - Atif Shehzad Back To Top

Hi Ayisha, Please further elaborate your scenario with examples. I do not get enough ides of problem.

Thanks


Monday, October 07, 2013 - 1:34:33 PM - Ayisha Chaudhury Back To Top

Hello,

 

I am reading through this page and am trying to connect the dots........

I have migrated to a new server where the tables, views etc have been migrated (by another team much more experienced)  I am now having trouble running scripts which worked in the old server and do not work in the new server.

 

I am assuming the above rectifies this issue by setting up the original properties, functions etc in the new server by running the script created by the above process??

Please help it would be much appreciated, thanks

Ayisha


Thursday, December 13, 2012 - 5:13:01 AM - jake Back To Top

thanks a lot


Friday, November 05, 2010 - 8:13:09 AM - glynne smith Back To Top

I've used this option frequently, but never found a way to get it scheduled as a job so I can run it weekly!

Any ideas?

Thanks


Learn more about SQL Server tools