0

I know Azure does its own backups in the cloud. However, due to company policy I need to generate a local backup copy of the database and be date-time stamped.

I've read this, and it has allowed me to create a .bacpac file and import it into our on-prem SQL server (2019). What I want is a way to save the bacpac file on a network folder, on a regular basis.

UPDATE - no I don't have to, store the bacpac file in an on-prem database. I only mentioned it to say, yes I can do this extra step. What I really want is to simply save the bacpac file, date-stamped in the filename, and in a network folder on-prem.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Fandango68
  • 4,461
  • 4
  • 39
  • 74
  • 1
    Can't you just save the BACPAC in the timestamp format and keep that to meet policy? It is "restorable" to any SQL version (Azure or SQL Server) for recovery purposes. Why do you need the extra effort? – Martin Cairney Mar 17 '21 at 05:38
  • 1
    Hi @Fandango68, If you want to refresh or sync the data in the database on a regular basis, do you think about using Azure SQL database [data sync](https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure)? Create or import backup database to your on-prem SQL Server, then using data sync to refresh/sync the data between Azure SQL and on-prem SQL Server. – Leon Yue Mar 18 '21 at 02:16
  • @MartinCairney - exactly what I want to achieve. See my update – Fandango68 Mar 19 '21 at 00:42
  • @LeonYue - no good unfortunately, as I don't own the Azure tenancy to set it up. I looking for solutions to "pull" the database down to us on-prem – Fandango68 Mar 19 '21 at 00:52
  • @Fandango68 If you want backup the Azure database, you at least need database admin permission. Since you are using database/server admin, you can use data sync feature. It's free. – Leon Yue Mar 19 '21 at 01:29
  • Hi @Fandango68, do you have any other concerns? May I post it as answer? – Leon Yue Mar 22 '21 at 01:12
  • 1
    You appear to have answered your question with the update. Use a script to call sqlpackage.exe to extract the BACPAC and use configure the output destination and filename according to the extract date. – Martin Cairney Mar 22 '21 at 07:07
  • @MartinCairney could you please answer this, with some details on how to use the sqlpackage.exe? That is the answer really. Sorry Leo. I do not have access to the external provider's tenancy, to use data sync and they won't do it for me without $$$ – Fandango68 Mar 24 '21 at 00:38
  • 1
    Hi @Fandango68, do you mind use third-part tool to regular backup Azure SQL database to local? Please ref this blog: https://sqlbackupandftp.com/blog/how-to-backup-azure-sql-database-to-local-machine#sqlbackupftp. It provides all the way to backup the Azure database to local, include regular backup features. The output .bacpac backup file name schama example like this: `Mydatabase202103250956`,databasename+date. – Leon Yue Mar 25 '21 at 02:00
  • See https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?WT.mc_id=DP-MVP-5001259&view=sql-server-ver15 For performance I'd suggest a VM in Azure to run it from and export the bacpac to Azure Storage – Martin Cairney Mar 25 '21 at 05:48

1 Answers1

1

If you don't mind use third-part tool to regular backup Azure SQL database to local, please ref this blog: How to backup Azure SQL Database to Local Machine. It provides all the way to backup the Azure database to local, include regular backup features.

This blog provide the tools SqlBackupAndFtp to help us regular backup the database. The output .bacpac backup file name schema example like this: Mydatabase202103250956, databasename+date.

It also give the tutorial to Backup Azure SQL Database Using BCP Utility:

bcp sqlftpbackupdb.SalesLT.CustomerAddress out c:\sqlfile\cust.dat -c -U daniel -S tcp:sqlftpbackupserver.database.windows.net

You also could ref the official document: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-export?WT.mc_id=DP-MVP-5001259&view=sql-server-ver15.

HTH.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23