SQL Server Backup – Difference Between .bak, BACPAC, and .mdf Files

backupmaintenancesql server

What is the difference between a .bak file as created when backing up a database, vs just copying the .mdf files and restoring as mentioned over here, vs creating a BACPAC file.

In this case, the logs themselves are not important, and the .bak file is far larger than the actual database. (a 20GB backup on a 2GB database).

For this backup strategy (copying database files themselves) I would detach the database on a daily basis, copy the mdf files across to an external media and then reattach the database. There would be 3 backups of each database (with older backups deleted as newer backups are created):

  • db_daily_backup
  • db_weekly_backup
  • db_monthly_backup

I realise that MS SQL provides it's own backup mechanism, that is almost certainly superior to what I've outlined. However the bloat caused on even differential backups seems too much for what we need and the amount of space we have available.

Best Answer

the .bak file is far larger than the actual database.

The problem might be that you are just appending the backups to one single file. The default is to appended to the device. Take your backups with WITH COMPRESSION, INIT - compress your backup and all backup sets should be overwritten.

A .bacpac file is not a substitute for a full backup.. Also, using MDF/LDF files as backup is not a good habit.

Related Question