Azure SQL nightly backup and restore, automated

Question

Hi all -

I have in Azure a single SQL server with two databases, Production and Staging. I'd like to have a scheduled task that ran once per night to backup the live database, delete the staging one and restore the live backup AS the staging. So that each day, the staging site is running with yesterday's production data.
It looks likely I can concoct some form of Powershell script which will do this, although how I schedule that and where it runs is currently beyond me. Before I get into learning something new, is there anything existing someone could recommend that will achieve this for me?

Thanks
Michael

Answer 1

@Michael-3291

There are 2 parts to your question:

1. Options for Copying the Azure DB
2. Automating the copy and restore

Coming to the first part there are several ways of achieving this. You can use powershell commands for either

1. Export or Import a Database
2. Copy a Database on same server or a different server

Second part of the question how to automate the powershell or the scripts. You can use Job Automation or Elastic jobs to do so.

Please let us know if you need more information.

Thanks
Navtej S

Answer 2

HI @Michael-3291

What i know Azure SQL is fully management as services this means Azure taking care about many thing one of them the backup are taken automatic

All Basic, Standard, and Premium databases are protected by automatic backups. Full backups are taken every week, differential backups every day, and log backups every 5 minutes. The first full backup is scheduled immediately after a database is created

These backups are retained for 7 days for Basic, 35 days for Standard and 35 days for Premium