2

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.

5

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.

  • Thanks. Why is a .bacpac not suitable for a backup if it is a snapshot of the entire database? – Zach Smith Nov 10 '16 at 18:00
  • A full backup contains a complete copy of database and provides a single point-in-time to which the database can be restored (datafile + log file) vs a bacpac is just user data + schema (mainly used for archiving or migrating from onprem to cloud). Remember that bacpac's are not transactionally consistent. So best is to use sql server native backups. – Kin Shah Nov 10 '16 at 19:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.