Diff between .bak and .backpac files?

  • 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

  • "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"

    See here.

  • 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
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply