April 9, 2018 at 5:22 am
Hi All,
What are the differences between on-prem sql native backups and Cloud based Backpac file?
When I tried to take a normal backup on Azure SQL database, it throws an error saying this command is not supported in this version.
Other thing is, why the backup .bak file size more when compared to backpac file. IS there any technical difference on how they take backups?
On-prem backups takes backup at page or extent level, what about backpac files? how it takes the backup?
Thanks in advance.
Sam
April 9, 2018 at 5:29 am
"A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from a SQL Server database. A BACPAC file can be stored in Azure blob storage or in local storage in an on-premises location and later imported back into Azure SQL Database or into a SQL Server on-premises installation"
April 9, 2018 at 9:33 am
A backup is a page by page copy of your database that takes into account the fact that active transactions are occurring so that it has a recovery phase when a restore is run that allows for full ACID compliance of your data at the end of a restore. As Beatrix has already said, the BACPAC is just an export of your data and structures stuck into a zip file. That export of the data is not, in any way, aware of the fact that transactions could be occurring at the time the BACPAC is created. Therefore, when restoring a BACPAC, you could be getting data that is corrupted (deletes half completed, stuff like that) and not ACID compliant. Because of this, it's strongly recommended that you only, ever, take a BACPAC from a database without connections. The best way to deal with this is to create a copy of your database (presumably within Azure) and then create the bacpac from the copy.
They are not the same and are not interchangeable.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 10, 2018 at 4:49 am
I have done a summary of backup and restore options for Azure SQL DB.
https://blobeater.blog/2018/03/26/backup-and-restore-options-for-azure-sql-db/
As Grant says if you do the bacpac get a copy first ( steps shown in the above link).
thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply