Lift and Shift SQL server database backups on Azure

Question

HI,

Can we schedule daily database backups on Azure portal for lift and shift SQL server which is in AZURE.

Thank you

Answer 1

What exactly are you targeting?

• SQL Server running on a VM in Azure?
• SQL Server Managed Instance?
• Azure SQL Database?

For the first two you can do BACKUP/RESTORE. I don't think you can schedule that from the Portal (but I could be wrong), but you could schedule it from SQL Server Agent.

For Azure SQL Database, you cannot do Backup/Restore, but you need to use Bacpacs or SQL Server Migration Assistant (SSMA). I guess you could schedule loading of Bacpacs with help some Powershell scripting. (No, I don't have any example.)

Answer 2

Hello @Newtojob,

As Erland mentioned, what is you real environment? SQL server on Azure VMS or Azure SQL database or Azure SQL managed instance?

For SQL server on Azure VMS, the following screen table provides information on various backup and restore options for SQL Server on Azure VMs. As Erland mentioned that we can use agent to schedule backup. For detail backup steps and requirement information, please refer to MS document Backup and restore for SQL Server on Azure VMs.

For Azure SQL database, database backups are executed automatically and it is not possible to change this behavior. This is a service offered when you create a SQL Azure database, and the first full backup occurs immediately after you create a new SQL Azure database and the rest of the backups are scheduled by SQL Azure itself. We cannot see the schedule, but Automated backups include Full backups which are taken every week, Differential backups which are taken once in 12 to 24 hours and Log backups which are taken every 5 to 10 minutes. This means that all SQL Azure databases are using Full Recovery Model, so we can always restore to a point-in-time. The backup retention period depends on the SQL Azure database service-tier. Basic service-tier offers a 7 days retention policy and Standard and Premium service-tiers offer 35 day retention policies.

For Aure SQL managed instance, database backups are executed automatically as Azure SQL database, full backups which are taken every week, Differential backups which are taken once in 12 to 24 hours and Log backups which are taken every 5 to 10 minutes. The exact timing of all database backups is determined by SQL Managed Instance service as it balances the overall system workload. You cannot change the schedule of backup jobs or disable them. But if you have a different requirements rather than automated backup comes with Azure SQL Managed Instance PaaS offering, you can schedule copy_only backup for one/all databases in your Azure SQL managed instance using sql agent job. Please refer to this MS blog to get detail steps.

 

Best regards,

Cathy

---

If the answer is helpful,  please click "Accept Answer" and kindly upvote it.