How to copy Azure SQL database to on-premises SQL Server daily for reporting? I have a database as a service SQL instance in Azure, I want to copy this database to a reporting server located in a virtual machine, and this is not SQL as a service, so my COPY statement won't work, this is what I have:



I'm tryin to copy from dbserver-prod to the server marked in the red rectangle

This is the statement I've used to copy from SQL AAS instances and has been working fine but now I get an error: :

CREATE DATABASE Database_Reporting AS COPY OF [dbserver-prod].[database];


My aim is to have prod database copied to this server once a day for reporting. Any thoughts on how this can be done will be very helpful.
You can't copy direct from AzureSQL to on-prem SQL (even on-prem SQL running in an Azure VM) that way.

To copy the database manually you need to export it via SSMS (or the relevant command line tool) or the Azure portal, transfer the resulting bacpak file to the target server and import it there using SSMS or the relevant command-line tools. If this is to be a regular thing (weekly or daily to provide a regularly updated reporting copy?) then you'll want to try automate that via powershell script run from task scheduler or similar.

Is there a reason that you want your extra copy in an on-prem SQL Server install instead of also being in AzureSQL? If not then you might consider changing that as CREATE AS COPY OF is likely to be significantly quicker than export+transfer+import.

Another option after creating your initial copy is to use SSIS to update it if it is easy to work out (from audit trail records, change tracking, etc.) what data has changed since the last update. This may be far more efficient as you only need to transfer new/changed data, though it relies on you keeping the database schema on the on-prem side identical to the production schema in AzureSQL. You may also wish to look into Azure Data Sync (currently in preview): https://docs.microsoft.com/en-us/azure/sql-database/sql-database-sync-data