Backup SQLServer database with MDF file and only .Bak file

Dear brother and sister, for my SQLServer database i always backup only .Bak file without MDF file and it always succeed to restore. But i just wonder what is different between backup only .Bak file and both of .bak and .mdf file? Witch one is better? and why it better?

Thanks in advance for any advice.
Sok DymangDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
SQL Server backup - the .bak file IS the backup copy of the MDF. Restoring it, essentially reestablished the MDF. So your physical disk backup, should be the BAK file. Depending on when that happens, your backup of disks will probably error on the MDF because it is in use....

The reason for backup is a restore point. And understanding how much data might be lost / compromised... Typically .bak (and  frequent log backups) are enough to recover.

Does that make sense ?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
Witch one is better? and why it better?
personally i would prefer a .bak approach, why? it's because it got more features we can specify.
you can check those features in General and Options tabs.

Image copied online
while mdf could be used in attaching or detaching approach of your database. a simple way to go
yo_beeDirector of Information TechnologyCommented:
When you say which is better are you referring to backing up MDF as if it is a file like word or pdf?  If that is the case this not an option unless you detach or take the DB  offline first then back it up.  If you are using SQL Agent or a backup software that has the SQL agent then it will backup the DB as a BAK.  Using a standard MSSQL backup plan is the recommended method.
Need More Insight Into What’s Killing Your Network

Flow data analysis from SolarWinds NetFlow Traffic Analyzer (NTA), along with Network Performance Monitor (NPM), can give you deeper visibility into your network’s traffic.

Mark WillsTopic AdvisorCommented:
Worthwhile reading : https://www.brentozar.com/archive/2007/10/backing-up-sql-server-my-own-mediocre-practices/

Bit old, but mostly still applies.

Also read the discussions, you will note that people still refer to it (this year).

P.S. @yo_bee, enjoying your posts and would enjoy reading some profile insights :)
ste5anSenior DeveloperCommented:
The .MDF file is a databse file, while a .BAK is a backup file made by SQL Server. The differences are:

When you copy a .MDF file in OS, then you don't know the state of the database and database file. Not all transactions maybe written to it, so you may loose data. Cause transactions are first written to the log (.LDF). The time between transaction are written to the datafile can vary. Thus you never know, how many transaction or how much data is missing in the .MDF right now. Much more important, you can copy that file during a file reorganization. Then you may even loose further data. So the copy of a .MDF can be in an inconsistent state.
And you can read that file during a SQL Server write operation, which means that you can read a file, which is corrupted for other instances.

A backup file must be created by using the BACKUP command. The most important point is - as Mark alread pointed out - that you tell SQL Server that you want a BACKUP from the current state. This means SQL Server now copies the data into that .BAK which representes the current state. Hereby it guarantees that it is also in a consistent state (ACID principles).
Further more a .BAK file only contains the data to build a .MDF and .LDF file during a restore operation, thus it does not contain certain mangement structures or the free spaces from the fill factor settings. And it can be compressed. So a .BAK is typically much smaller than a .MDF.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
The only truly reasonable, valid method is the .BAK/backup file.  

That backup contains .MDF data and as much .LDF data as is necessary to recover the database up to the time consistent with the backup.  This is critical because otherwise you could get data inconsistencies that make the db unuseable.

With that backup, you can also use differential backups and/or transaction log backups to give you a more recent recovery time, without having to back up the entire db again.  Naturally that is not possible with a .MDF file(s) copy.

I suppose you could take the db offline to copy it, but that's too risky, because:
1) if any file has consistency errors, the db may not reattach at all
2) if a reattach fails, it could modify the files, making them unable to reattach; that is, you must have two copies of the files, one to reattach and one to act as a backup in case the attach somehow fails
3) once SQL no longer has the file locked, other programs/activities could modify the SQL files, damaging them and making them unusable.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.