Export Microsoft SQL Database to Azure SQL
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!
8) Once transfer is completed you can login to Management Studio with Azure SQL Server credentials or login to Azure portal to view new exported DB.
Error:
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: Error validating element [user]: The element [user] has been orphaned from its login and cannot be deployed.(Microsoft.SqlServer.Dac)
Troubleshoot & Findings:
Orphaned users in SQL Server occur when a database user is based on a login in the master database, but the login no longer exists in master. This can occur when the login is deleted, or when the database is moved to another server where the login does not exist. This topic describes how to find orphaned users, and remap them to logins.
Source: https://docs.microsoft.com/
Solution:
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
Error report would have told you what owners you need to delete. Select the owner and click remove in the bottom right.
Expand your database -> Security -> Schemas > Change Schema Owner
Right click on the schema that is owned by the user you want to delete and change the owner (Properties -> General -> Schema Owner).
Than you will need to delete the ‘Users’ from under the ‘Security’ section.
