Azure Bacpac file Restore Error

Question

I do the daily bacpac file backup from Azure database using RedGate Azure Backup tool. Until 31st of May, All my Bacpac files can restore to Local SQL2008R2 database using DAC Client Tool.

Starting from 1st of June, I got error like:

Failure Creating schema objects in database
'XXXXX' System.Data.SqlClient.SqlException (0x80131904): Cannot
find the object "dbo.XXXX" because it does not exist or you do not have per
missions.

When I check it table still exist in DB.Also No idea for this. Is there anyone face that kind of missing table, invalid Column.. etc

Answer 1

RedGate Azure Backup is deprecated now. The DAC Client Tool (DacCli.exe) has been integrated into SSMS 2012 and SSDT. You could try updating your DAC Client Tool to the latest version (1.6 from 1.2)

SSMS 2012 Data Tools now allow you to Import Data-tier Applications. Take a look at the Data-Tier Application Framework Feature in SQL 2012 downloads. View this MSDN article for reference.

Answer 2

I use the free utility SQL Backup and FTP to backup Azure SQL Databases to a local .bacpac file. The free version allows you to backup up to 2 different databases and then the paid versions are very reasonable (i.e. Professional version is $70 or so).

http://sqlbackupandftp.com/

It actually runs on a schedule for me, automatically emails me the results of the backup and stores it on my Google Drive (even with the free version - I just have it store the timestamped backups in the file folder of my Google drive).

The procedure I use to restore it to a local database is:

1. Back up SQL Azure to .bacpac file
2. Login to local database server as sa
3. Right-click Databases
4. Click Import Data-Tier Application
5. Select .bacpac file either from Azure or local disk
6. Enter the name and the locations to store the data and log files
7. If necessary, set the original database to single user mode and delete it
8. If necessary, rename restored database to original database name

Note: Must use SQL Server 2012 (or later) management tools