When I try to restore a BACPAC created by Sql Backup and FTP I get a “File contains corrupted data” error.
I’m using SqlPackage.EXE to restore the BACPAC to a local SQL Server. If I use SqlPackage to create the BACPAC, the restore works.
Big Picture: we have an Azure SQL database that is used for a live website. We backup the database to a local server using Sql Backup and FTP every night. We want the ability to restore the BACPAC file to a local SQL Server for website development and testing purposes.
Sorry, but there is no way to use SQLBackupAndFTP to backup your Azure SQL databases and restore them to your SQL Server. We can suggest you the following workaround:
SqlPackage.exe is a command-line utility, it means that you can use “Run custom scripts” option at SQLBackupAndFTP and add a script that will be performed after the backup.
For easier searching your last backup you can use “Customize backup file name” option at the “Compression Settings” window.
Just to clarify, are you saying that the BACPAC created SQL Backup and FTP is “unique” and can’t be restored using normal SQL Server?
I know that I can just use SqlPackage.EXE to backup my Azure SQL database. I chose to purchase SBAF (SQL Backup and FTP) because it made the whole process easier. But if the BACPAC that SBAF creates can’t be restored, then I will have no choice other than return the program and just use SqlPackage.
SQLBackupAndFTP creates BACPAC file in the same way (standard) as you do it with Azure. SQLBackupAndFTP can restore such backups automatically to your Azure SQL databases but if you need to restore BACPAC files to your Local SQL Server please use the workaround described above. Just add the script to your backup job to restore your BACPAC files to your Local SQL Server databases automatically.
I think we were having a miscommunication. I wasn’t trying to use SBAF (SQL Backup and FTP) to do the actual restoring of the BACPAC file to a local SQL Server. The issue is that BACPAC file could not be restored.
In my testing I could see that SBAF created a BACPAC file, but I was having difficulty restoring it. I always like to test my backups by making sure that I can restore them.
I thought the issue might be that SBAF created special BACPAC files that could only be restored using SBAF and not with other tools like SqlPackage.EXE.
Today I tried restoring a different BACPAC file (created by SBAF) and the restore worked. So I think the issue was just a bad backup. Thank you for replies.
I am receiving an error when executing a DB restore. The backup was created from local SQL server. I am now trying to restore the DB to Azure SQL DB and when executing I receive the following: “Job execution error: Could not load package from <…file.bak… > File contains corrupted data. Computer name: …”
Could you please install the following Alpha edition http://sqlbackupandftp.com/download/alpha and choose Microsoft SQL Server (remote) connection and at the “Microsoft SQL Server Remote Backup Options” select the type of target base as on the screenshot.
Hi Alexander. Please be advised, I’n not trying to backup the Azure SQL DB. I have a local SQL Server DB that I have already backed up using this software. The Out SQL file options in the screenshot you show does not have these options available when I backup the local SQL DB. I am trying to restore the local SQL DB backup to Azure SQL DB. The Output SQL File Options you show in the screenshot above is for backing up the Azure SQL DB which this option is not available when trying to restore to Azure SQL DB. Is this backup restore process I mention here supported by this software?
When I try the restore job with version 12.2.9.17 Pro I now receive the following error: 10/11/2019 11:19:12 AM [Error]: Failed to restore ******* database. Unexpected backup file extension “.bak” for server type “AzureSqlServer”.
Again, this backup was created from a local SQL Server DB. Not sure if this backup and restore process is supported? Any suggestions what I should try next?
Could you please connect to your local SQL Server using Microsoft SQL Server (remote) connection and run a backup using the settings from the screenshot above.
Then please run a restore to Azure using Microsoft SQL Server (remote) connection.
Hi Alexander,
My version that you said to download and install does not have the More setting => Output SQL file options screen you show in your previous post. I’ve included a screenshot of the version I have installed (12.2.10.1).
I now receive an error that says 10/20/2019 6:51:51 PM [Error] Failed to restore xxxxxx database. Unexpected backup file extension “.sql” for server type “AzureSqlServer”.
