How to: Use Generate and Publish Scripts Wizard to Migrate a Database to Azure SQL Database
Updated: October 16, 2014
The Generate and Publish Scripts Wizard can be used to create Transact-SQLscripts for SQL Server database and/or related objects within the selected database. You can then use the scripts to transfer schema and/or data to Microsoft Azure SQL Database.
Before you begin:
Use the Generate and Publish Scripts Wizard to:
Migrate a Database
Before You Begin
Using the Generate and Publish Scripts wizard to migrate a SQL Server database to Azure SQL Database should be limited to:
Teams who have experience with the wizard.
Migrating simple databases that need few schema changes to run on Azure SQL Database. The scripts generated from the source database can be modified before being used to create the new version of the database on Azure SQL Database, but using a database project in the SQL Server Data Tools has richer support for making schema changes.
Migrating small databases that do not have much data. The wizard generates scripts that use insert statements instead of bulk copies to transfer the data. The insert statements can be throttled when the tables contain too much data, and are not as fast as bulk copies.
Be careful when choosing options in the wizard. It is easy to select an option that prevents the transfer of important information, such as the option to not generate indexes.
The Generate and Publish Scripts Wizard is installed with SQL Server. Use the wizard from SQL Server 2008 R2 or later.
Migrate a Database by Using the Generate and Publish Scripts Wizard
Using the wizard involves the following main steps:
Open SQL Server Management Studio and connect to an instance of the Database Engine.
In Object Explorer, right click a database to open a menu, select Tasks…, and then select Generate Scripts.
Choose objects to export.
Set scripting options. You have the options to save the script to file, clipboard, new query window; or publish it to a web service.
Set advanced scripting options.
By default, the script is generated for stand-alone SQL Server instance. To change the configuration, click the Advanced button from the Set Scripting Options dialog, and then set the Script for the database engine type property to SQL Database.
You can also set the Types of data to script to one of the following based on your requirements: Schema only, Data only, Schema and data.
After the script is created, you have the option to modify the script before running the script against a Azure SQL Database to transfer the database.