Muokkaa

Jaa

Generate Scripts Wizard

  • Artikkeli

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

This article teaches you to use the Generate Scripts Wizard to create scripts to transfer a database between instances of SQL Server, Azure SQL Database, or Azure SQL Managed Instance.

You can generate scripts for a SQL Server database in your local network, or from Azure SQL. The generated scripts can be run on another instance of SQL Server or on Azure SQL. You can create scripts for an entire database, or limit it to specific objects.

For a more detailed tutorial on using the Generate Scripts wizard, see Tutorial: Generate Scripts Wizard.

Prerequisites

The source and target database can be on Azure SQL Database or Azure SQL Managed Instance, or an instance of the Database Engine running SQL Server 2005 (9.x) or later versions.

Permissions

The minimum permission to generate scripts is membership in the db_ddladmin fixed database role on the origin database.

Use the Generate Scripts Wizard

  1. In Object Explorer, expand the node for the instance containing the database to be scripted.

  2. Right-click on the database you want to script, and select Tasks > Generate Scripts.

    Screenshot of Generate Scripts Wizard.

  3. Complete the wizard dialogs:

Introduction page

This page describes the steps for generating a script.

Do not show this page again - Skip this page the next time you start the Generate Scripts Wizard.

Screenshot of Introduction Page.

Choose Objects page

Use this page to choose which objects you want to include in the scripts generated by this wizard.

Script Entire Database Option - Select to generate scripts for all objects in the database and to include a script for the database itself.

Screenshot of script entire database.

Select specific database objects - Select to limit the wizard to generate scripts for only the specific objects in the database that you choose:

  • Database objects - Select at least one object to include in the script.

  • Select All - Selects all available check boxes.

  • Deselect All - Clears all the check boxes. You must select at least one database object to continue.

    Screenshot of specific script.

Set Scripting Options page

Use this page to specify if you want the wizard to save scripts to the location of your choice.

Options - If you want the wizard to save scripts to a location of your choice, select Save scripts to a specific location. You can later run the scripts against either an instance of the Database Engine, or against SQL Database.

Save Scripts to a Specific Location - save one or more Transact-SQL script files to a location you specify.

Screenshot of Save as notebook.

  • Save as notebook - Save the script to one or more .sql files. Select the browse button (...) to specify a name and location for the file.

  • Save as script file Save the script to one or more .sql files. Select the browse button (...) to specify a name and location for the file. Select the Overwrite existing file check box to replace the file if one already exists with the same name. Select Single script file or One script file per object to specify how the scripts should be generated. Select Unicode text or ANSI text to specify the kind of text that should be used in the script.

  • Save to Clipboard - Save the Transact-SQL script to the Clipboard.

  • Open in new query window - Generate the script to a Database Engine Query Editor window. If no editor window is open, a new editor window is opened as the target for the script.

  • Advanced - Display the Advanced Options dialog box where you can select advanced scripting options.

Advanced Scripting Options page

Use this page to specify how you want this wizard to generate scripts. Many different options are available. Options are greyed out if they aren't supported by the version of SQL Server or SQL Database specified in Database engine type.

Screenshot of Advanced Options.

Options - Specify advanced options by selecting a value from the list of available settings to the right of each option.

General - The following options apply to the entire script.

  • ANSI Padding - Includes ANSI PADDING ON in the script. The default is False.

  • Append to file - When True, this script is added to the bottom of an existing script, specified on the Set Scripting Options page. When False, the new script overwrites a previous script. The default is False.

  • Check for object existence - When True, adds the existence check before generating the create statement for your SQL Objects. For example: tables, views, functions, or stored procedures. The CREATE statement is wrapped in an IF statement. If you know your target is clean, the script is a lot cleaner. If you do NOT expect the objects to exist on the target, you get an error. The default is False.

  • Continue scripting on error - When False, scripting stops when an error occurs. When True, scripting continues. The default is False.

  • Convert UDDTs to base types - When True, user-defined data types (UDDT) are converted into the underlying base data types that were used to create them. Use True when the UDDT doesn't exist in the database where the script runs. When False, UDDTs are used. The default is False.

  • Generate script for dependent objects - Generates a script for any object that is required to be present when the script for the selected object is executed. The default is True for the Script entire database option, and False for Select specific database objects.

  • Include descriptive headers - When True, descriptive comments are added to the script separating the script into sections for each object. The default is False.

  • Include if NOT EXISTS - When True, the script includes a statement to check whether the object already exists in the database, and doesn't try to create a new object if the object already exists. The default is False.

  • Include system constraint names - When False, the default value of constraints that were automatically named on the origin database are automatically renamed on the target database. When True, constraints have the same name on the origin and target databases.

  • Include unsupported statements - When False, the script doesn't contain statements for objects that aren't supported on the selected server version or engine type. When True, the script contains the unsupported objects. Each statement for an unsupported object has a comment that the statement must be edited before the script can be run against the selected SQL Server version or engine type. The default is False.

  • Schema qualify object names - Includes the schema name in the name of objects that are created. The default is True.

  • Script binding - Generates a script for binding default and rule objects. The default is False. For more information, see CREATE DEFAULT (Transact-SQL) and CREATE RULE (Transact-SQL).

  • Script collation - Includes collation information in the script. The default is False. For more information, see Collation and Unicode support.

  • Script defaults - Includes default objects used to set default values in table columns. The default is True. For more information, see Specify default values for columns.

  • Script drop and create - When Script CREATE, Transact-SQL statements are included to create objects. When Script DROP, Transact-SQL statements are included to drop objects. When Script DROP and CREATE, the Transact-SQL drop statement is included in the script, followed by the create statement, for each scripted object. The default is Script CREATE.

  • Script extended properties - Includes extended properties in the script if the object has extended properties. The default is True.

  • Script for engine type - Creates a script that can be run on the selected type of either SQL Database or an instance of the SQL Server Database Engine. Objects not supported on the specified type aren't included in the script. The default is the type of the origin server.

  • Script for server version - Creates a script that can be run on the selected version of SQL Server. Features new to a version can't be scripted for earlier versions. The default is the version of the origin server.

  • Script logins - When the object to be scripted is a database user, this option creates the logins on which the user depends. The default is False.

  • Script object-Level permissions - Includes scripts to set permission on the objects in the database. The default is False.

  • Script statistics - When set to Script Statistics, this option includes the CREATE STATISTICS statement to re-create statistics on the object. The Script statistics and histograms option also creates histogram information. The default is Do not script statistics. For more information, see CREATE STATISTICS (Transact-SQL).

  • Script USE DATABASE - Adds the USE DATABASE statement to the script. To make sure that database objects are created in the correct database, include the USE DATABASE statement. If you expect to use the script in a different database, select False to omit the USE DATABASE statement. The default is True. For more information, see USE (Transact-SQL).

  • Types of data to script - Selects what should be scripted: Data only, Schema only, or both. The default is Schema only.

Table/View Options - The following options apply only to scripts for tables or views.

  • Script change tracking - Scripts change tracking, if enabled on the origin database, or tables in the origin database. The default is False. For more information, see About Change Tracking (SQL Server).

  • Script check constraints - Adds CHECK constraints to the script. The default is True. CHECK constraints require data that is entered into a table to meet some specified condition. For more information, see Unique constraints and check constraints.

  • Script data compression options - Scripts data compression options if they're configured on the origin database, or tables in the origin database. For more information, see Data compression. The default is False.

  • Script foreign keys - Adds foreign keys to the script. The default is True. Foreign keys indicate and enforce relationships between tables.

  • Script full-text indexes - Scripts the creation of full-text indexes. The default is False.

  • Script indexes - Scripts the creation of indexes. The default is True for the Script entire database option, and False for Select specific database objects. Indexes help you find data quickly.

  • Script primary keys - Scripts the creation of primary keys on tables. The default is True. Primary keys uniquely identify each row of a table.

  • Script triggers - Scripts the creation of DML triggers on tables. The default is False. A DML trigger is an action programmed to execute when a data manipulation language (DML) event occurs in the database server. For more information, see DML Triggers.

  • Script unique keys - Scripts the creation of unique keys on tables. Unique keys prevent duplicate data from being entered. The default is True. For more information, see Unique constraints and check constraints.

Summary page

Screenshot of GS Summary.

This page summarizes the options that you selected in this wizard. To change an option, select Previous. To begin generating scripts, select Next.

Review your selections - Displays the selections you made for each page of the wizard. Expand a node to see the selected options for the corresponding page.

Save Scripts page

Use this page to monitor the progress of the wizard as it occurs.

Details - View the Action column to see the progress of the wizard. After the wizard generates the scripts, it saves the scripts to a file. When each of these steps is complete, select the value in the Result column to see the outcome of the corresponding step.

Save Report - Select to save the results of the wizard's progress to a file.

Cancel - Select to close the wizard before processing is complete, or if an error occurs.

Finish - Select to close the wizard after processing is complete, or if an error occurs.

Save scripts

Screenshot of Finish.

If all the settings are correct, your configuration finishes successfully.

Generate scripts on Azure Synapse Analytics

If the syntax generated when using "Script As..." doesn't look like Azure Synapse Analytics syntax or if you receive an error message, you might need to set your scripting options in SQL Server Management Studio to Azure Synapse Analytics.

How to set default scripting options to SQL Data Warehouse

In order to script objects with Azure Synapse Analytics syntax, set the default scripting option to Azure Synapse Analytics as follows:

  1. Select Tools then Options.
  2. Select SQL Server Object Explorer, and Scripting.
  3. Under Version options, set Match script settings to source to False.
    1. Set Script for database engine type to Microsoft Azure SQL Database Edition.
    2. For dedicated SQL pools in Azure Synapse Analytics, set Script for the database engine edition to Microsoft Azure SQL Data Warehouse Edition.
  4. Select OK.

How to generate scripts for SQL Data Warehouse when it's not the default scripting option

If you set Azure Synapse Analytics as your default scripting option as shown previously, these instructions can be ignored. However, if you choose to use different default scripting options, you might encounter an error. To avoid errors, follow these steps in order to Generate Scripts for Azure Synapse Analytics:

  1. In Object Explorer, right-click your dedicated SQL pool under Databases.
  2. Select Generate Scripts.
  3. Choose Objects you wish to script.
  4. In Scripting Options, select Advanced. Under General set:
    1. Script for the database engine type: Microsoft Azure SQL Database.
    2. Script for the database engine edition: Microsoft Azure SQL Data Warehouse Edition.
  5. Select Save Scripts then Finish.

The options set in Step 4 aren't remembered. If you prefer for these options to be remembered, follow the instructions in How to set default scripting options to SQL Data Warehouse.

Related content