patternsqlMinor
SQL Server: What is the difference when backing up via .bak, BACPAC or just copying .mdf files?
Viewed 0 times
mdfthewhatsqljustbackingdifferencefilesviacopying
Problem
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):
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.
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.
Solution
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
A .bacpac file is not a substitute for a full backup.. Also, using MDF/LDF files as backup is not a good habit.
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.
Context
StackExchange Database Administrators Q#154862, answer score: 5
Revisions (0)
No revisions yet.