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:
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.
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.
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.
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:
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:
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.
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.
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.
Below is a sample screen shot of the script generated in SSMS. You can also review the generate script here.
Next Steps
- Here is a description of all script options for SSMS 2005
- Here is a description of all script options for SSMS 2008
- Review more information about SQL Server database compatibility levels
- Review more information about vardecimal storage format
