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.
- 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!
1) Error SQL71564: Error validating element [DATABASE]: The element [DATABASE] has been orphaned from its login and cannot be deployed.
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.
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.
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’.