There are many requirements like moving database between:
This is very easy to do, let me know what you think.
1. SQL Azure to local development SQL Server
2. SQL Azure to Virtual Machine SQL Server
3. Development SQL Server to Production SQL Server
4. Production SQL Server to Development SQL Server
In above all requirements a very generic option “Export Data-tier Application” and “Import Data-tier Application” will always work. So, in this post you will learn how to achieve above all requirements.
Right click on source database which you would like to move to another server (database server) and select Tasks | Export Data-tier Application.
Now select location to create BACPAC file.
This will take couple of minutes depending on your database size and at the end you will have BACPAC file. Now let's copy this file to target machine and then open SQL Server instance after that right click on ‘Databases’ | click on Import Data-tier Application.
Now select location you copied BACPAC file.
You will be prompted to change database name, change it now if you want then click on next to start restoration.
Restoration will also take couple of minutes depending on your database size. Then you will be prompted with success message.
Now you can see your tables and data on target server.
Hope this helps.