1

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

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.

Improve this question
4
  • That command is specific to SQL Azure DBs, and isn't available in regular SQL Server. Is this expected to be a one-time thing, or something you'll want to set up to be done on a recurring basis (once a day, week month, whatever)?
    – RDFozz
    Commented Nov 2, 2017 at 17:15
  • Thanks for your answer. This should be done periodically, maybe once a day yes. Thanks for your help.
    – fabian278
    Commented Nov 2, 2017 at 17:29
  • I'm no Azure expert, so I'll step out now - hopefully, someone else can help you. You may be able to help yourself as well - I'd search for something like (Azure local copy) as a starting point, or (Azure replicate local).
    – RDFozz
    Commented Nov 2, 2017 at 17:38
  • Just a side note, if that VM is in Azure, cool, if it's down on your local servers, you will be paying to move that data out of Azure. You may want to consider that when you set up your process.
    – Grant Fritchey
    Commented Nov 6, 2017 at 13:20

2 Answers 2

Reset to default
2

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://learn.microsoft.com/en-us/azure/sql-database/sql-database-sync-data

Improve this answer
1
  • Hello David, thanks for your answer. I need reporting so this will be done on a daily basis, I cannot copy as CREATE AS COPY OF because the on-prem server I'm targeting hast all reporting tools needed and the SQL instance doesn't, my reporting team could not make use of the copy if it's like that. I should look at some powershell scripts to help me automate this, thanks for your answer. If you have any further recommendations on how I should proceed in order to fully automate this process please share :)
    – fabian278
    Commented Nov 2, 2017 at 17:57
1

My main problem was moving data from one database to another, I found that Azure Sync Group does the trick in a pretty easy way, can run scheduled and you don't need a single line of code to accomplish this.

You can move data from Azure SQL to on-premises or the other way round. All the information you will need is under this link: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-get-started-sql-data-sync

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.