Moving SQL Database to In-Premise from Azure? Consider this first.

You can find many articles on the internet for moving to SQL Database but very less on moving to in-premise from SQL Azure. SQL Azure is a good product but sometimes you will need your database to move or copied over to in-premise SQL Server for a task. SQL Azure constantly updating itself and if your in-premise SQL Server in not of the latest version you might face error while trying to import it. This happens when developers are developing on SQL Azure so the code they had written was not tested for compatibility with in-premise. If you start importing the .bacpac file you might face only one error at a time. You fix that in SQL Azure and again start and export and then try importing it and again you face an error with another object. You will face only one error at a time even the database has more than one compatibility issues. So how to know how many such issues are there in SQL Azure database? To find that you have to do this check. Even before you start to export your database as a .bacpac file from Azure you need to check if it is compatible with the version of the target SQL Server you want to import it to. I am showing here an example to import database from Azure to SQL Server 2012 as 2012 is an older version and there are many things in SQL Server 2016 (or Azure) not compatible with SQL Server 2012. To check this you will need SQL Server Data Tools and the latest version of SQL Server Management Studio installed.

I will try to show you how to do this step by step.

1. Create a blank database, say "BlankDB", on the target in-premise SQL Server with the required compatibility.
2. Open SQL Server Data Tools from the start menu.
3. Add a connection to the Azure Database under SQLServer Objects Explorer. Under SQL Server Object Explorer right click on "SQL Server" and click on "Add SQL Server..."
   
4. In the Connect dialogue box expand Azure (you might need to enter your Azure credentials to list the databases) and select the database which you want to move out of Azure. Enter the credentials to connect to the SQL Server for the sysadmin user.
5. Similarly, add the connection to the BlankDB created.
6. Expand the Database folder under the In-Premise server to go to the BlankDB and right click on it. Under the menu click "Schema Compare..." option.
7. You will see a new window will appear in the workspace for SqlSchemaCompare with BlankDB already selected as a source. Now select "Select Target..." option from the drop-down next to it.
   
8. In the "Select Target Schema" window select the Database radio button and click "Select Connection..."
   
9. In the next window select the Azure database and click connect.
10. Click OK in "Select Target Schema" window.
11. Now we have selected both the databases.
12. Our target is Azure and source in In-Premise database and we want its inverse. Click the button in the middle of the source and the target database namely "Switch source and target".
    This will set our source and target databases correctly as below:
13. Now we are ready to compare so click the "Compare" button just above our source database.
14. Once the comparison is complete you will see the screen as below:
15. Now Click on the Generate Script button to generate the script of differences detected between two databases:
    
16. If the script generation found any error you will see that in "Data Tools Operations" windows. as below:
    
17. To see each issue in detail open the error list from View > Error List
18. In the error list window, you will find the exact database objects which are having compatibility issues with the target database.

Once you have fixed these issues you can re-run the same test if you want or just export the database as .bacpac file and import it to the in-premise server.