Sql Azure Backup & Restore Strategy

The fastest backup/restore strategy for Azure SQL databases?

If your database is 40GB it's long past time to consider having a redundant Database server that's ready to go as soon as the main becomes faulty.

You should have a second server running alongside the main DB server that has no actual routines except to sync with the main server on an hourly/daily basis (depending on how often your data changes, and how long it takes to run this process). You can also consider creating backups from this database server, instead of the main one.

If your main DB server goes down - for whatever reason - you can change the host address in your application to the backup database, and spend the 8 hours debugging your other server, instead of twiddling your thumbs waiting for the Azure Portal to do its thing while your clients complain.

Your database shouldn't be taking 6-8 hours to restore from backup though. If you are including upload/download time in that estimate, then you should consider storing your data in the Azure datacenter, as well as locally.

For more info see this article on Business Continuity on MSDN:
http://msdn.microsoft.com/en-us/library/windowsazure/hh852669.aspx

You'll want to specifically look at the Database Copies section, but the article is worth reading in full if your DB is so large.

Restore backup of an Azure SQL Database to another Azure SQL Database of another resource group

Azure SQL Database does not have a backup and restore (WITH REPLACE option) as we know it on SQL Server. We also cannot export a database and import it to an existing database.

You can import with a new name and then drop the existing database, and rename the name of the imported database to the name of the dropped database.

You can also use an initial sync of Azure SQL Data Sync to migrate all you data from one database to another. Not only your databases can be located on different resource groups, but also in different subscriptions. The initial sync runs faster on empty databases.

Azure SQL database backups and restore

Yes, if you are not using Failover Groups\Geo-replication then you have the default recovery option of Geo-restore (Restore-AzureRmSqlDatabase -FromGeoBackup). There is a delay between when a backup is taken and when it is geo-replicated to an Azure blob in a different region. As a result, the restored database can be up to one hour behind the original database. With Geo-restore the RTO is 12 h and the RPO is 1 h.

If restoring a database to an hour earlier can result in business liability or your organization cannot afford that, you should be using failover groups. This enables your database (and applications) to quickly restore availability in a different region in case of an outage.

With Auto-failover groups the RTO is 1 h ( to ensure that the failover is justified by the scale of the outage) and RPO 5s. With manual database failover, however, the RTO is 30 s and the RPO is 5 s.

Back up and restore Azure SQL Database schema

We can use bcp Utility for exporting and importing the data in a fast way.

SQL Azure Backup & Restore strategy

Backup/restore is a whole different animal in SQL Azure as the traditional backup/restore commands are not available. See the article SQL Azure Backup and Restore Strategy for a good overview and then read Copying Databases in SQL Azure. Also take a look at redgate's (beta) third party tool for SQL Azure Backup.

EDIT: Adding a reference to a new blog post that appeared today (Sept 29, 2011): Data backup strategies for Windows and SQL Azure

How to take Incremental backup for Azure SQL db using SQLPackage

Azure SQL Database supports Full backup, Differential backup and Transaction log backup.

Differential backup: - It is based on the latest full data backup. it only collects the data which are changed after the last full data backup. You may routinely backup your database without experiencing the burden of complete database backups.

Azure SQL Database provides automated backups.

Full Data Backup - every week;
Differential backup - every 12-24 hours;
Transaction log backup- every 5 to 10 minutes.

Reference: -
https://learn.microsoft.com/en-us/azure/azure-sql/database/automated-backups-overview?view=azuresql&tabs=single-database

Azure SQL Management instance - restore db different version

You can also use bcp to export the data from Azure SQL Database and then use bcp again or Import/Export Wizard or SSIS to import the data on the SQL Server VM.

You can use Azure Data Factory (ADF) to export tables to your SQL Server VM. ADF will create those tables and copy the data.