Export Microsoft SQL Database to Azure

As a developer who has moved a database to Azure, I know it can be a pain to find out exactly how to export a database in Microsoft SQL Server Management Studio. Therefore here is a step by step guide on how to export your database to Azure. There are also some pointers where I found trouble and if anyone else has some tips, please add them in the comments.

1) First and the simple step is to open SQL Server Management Studio.

2) Find the Database you wish to move to Azure and right click on it.

3) Navigate to ‘Tasks’ then to ‘Deploy to Windows Azure SQL Database’

4) When the first pop up comes up, you will just need to click next.

5) On the next screen you will need to connect to your Azure Server.

Required Details:

  • Server Name
  • Choose ‘SQL Server Authentication’
  • Enter the server Username
  • Enter the server Password

6)   Once connected enter in the New Database name you want to call it. The other setting I left alone, but you may want to change them as you wish. After all is entered you can click next in the bottom right.

7) You will then have a summary page before you press next again to start the transfer. You can then start the transfer!

 

Errors

1)  Error SQL71564: Error validating element [DATABASE]: The element [DATABASE] has been orphaned from its login and cannot be deployed.

FIX

This means that there is another owner on the Database. To fix this do the following:

Go to the target Database > Expand ‘Security’ > Expand ‘Roles’ > Expand ‘Database Roles’ > right click on ‘db_owner’ > click ‘properties’ as below

The report would have told you what owners you need to delete. Select the owner and click remove in the bottom right.

As well as this, you will need to delete the ‘Users’ from under the ‘Security’ section.

 

2)  Error SQL71564: The element Extended Property: [dbo].[VIEW_NAME].[MS_DiagramPane1] is not supported when used as part of a data.

FIX

ou will need to delete the extended properties on the views in question. Go to the View and right click on it. Click on properties then go to ‘Extended Properties’ on the side navigation. Select each property and delete it.

3) Error SQL71564: Table Table: [dbo].[TABLE] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.

FIX

To fix this you need to make sure the table has a primary key. To do this find the tale you that is the cause. Open up the database and right click on the ‘tables’. Select the design view and find the row you wish to make the Primary Key. Right click on the row you want to set to the Primary Key and click ‘Set Primary Key’.

 

Advertisements

3 thoughts on “Export Microsoft SQL Database to Azure

  1. Sachin Srivastava says:

    Thanks , that really help me if not all but to fix good number of issue while migrating db to azure platform. Thanks a ton for your effort for so easy explanation

    Like

  2. Virender Mehta says:

    While Migrating my database to Windows Azure i am getting this below error.Can Any one help me with some suggestions..
    Error SQL71005: Error validating element [dbo].[GetStockDetails_StoreWise]:
    The reference to the column ItemName could not be resolved.

    Error SQL71564: The permission ‘CONNECT’ is not valid for the target object
    ‘guest’ in Microsoft Azure SQL Database v12.

    Like

Leave a message please

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s