Search code examples
azure-sql-database

What is the best way to backup an Azure sql database?

I have an Azure SQL database that I want to backup offline, i.e. not on Azure. I need to create a .bacpac file first and move that file.

Now if I use az sql export etc .... this produces unexpected spike of DTU consumption in the performance and hence affects the database users as these are live database.

I was thinking of taking the last full backup and using that, but that is done weekly and hence would be 1 week old, where can I see that actual full backup?

It would be very helpful if I could get some ideas on the best way to backup the database to a .bacpac file and save on maybe another cloud service, how could I do this without affecting performance?

Solution

  • Since you don't want to cause impacts on your environment, the best approach would be to restore an existent backup.

    1. To check the last backup, you should navigate to the database you want to restore and click Restore database
    2. You will be presented with all the options to restore the database as a new one, and can see the earliest restore point and choose from there Point in time use
    3. Once you restore the new database, this database is not in use by your clients, so you can perform the "Export" operation without affecting the performance of the application by going to the new database, and click on Export Export database
    4. Specify the BACPAC filename, select an existing Azure storage account and container for the export, and then provide the appropriate credentials for access to the source database. A SQL Server admin login is needed here even if you're the Azure admin, as being an Azure admin doesn't equate to having admin permissions in Azure SQL Database or Azure SQL Managed Instance. Export database options

    To monitor the progress of the export operation, open the page for the server containing the database being exported. Under Data management, select Import/Export history.

    Sources: