Differences between .mdf & .ldf ?

[Ginka]

I'm trying to practice making some back ups and restoring data.
But I have some doubts.

What is the difference between *.mdf and *.ldf files??

When Im' goin to make a back up it ask me for a name, so which extension should I use?

 

[JohnDTampaBay]

Typically, MDFs are data files and LDFs are log files. Backups are a special type of data file usually have a BAK extension.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Ginka]

thanks a lot,
so, I have another question

Why should I need to back up the log file if I have backed up the *.mdf?

and
How can I back up the log file?

 

[JohnDTampaBay]

If your database is set to Full Recovery Mode, all transactions remain in the transaction log until manually truncated. You can use transaction log backups in combination with full backups to restore the database to a specific point in time, effectively applying transactions from the log up to the point you desire.

Using Enterprise Manager is the easiest. Right-click the database, choose All Tasks, then choose Backup Database. Also check out BACKUP in BOL.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[SQLBill]

Here is something important to remember....

You can NOT backup the .mdf and/or the .ldf files while SQL Server is in use. If you do so, they will be worthless. You must stop SQL Server services or detach the database(s) before backing up the .mdf/.ldf files.

-SQLBill

Posting advice: FAQ481-4875

 

[lovalles]

SQLBill you cannot backup this files with the service up if you use not a Database software agent, like windows backup.
But if you use Entreprise manager your can backup an online backup with users using sql and services up.

lovalles

 

[SQLBill]

lovalles,

You can backup the databases while they are in use, but that's not the same as backing up the .mdf/.ldf files, which is what the poster asked about. While the same data is being backed up, it's two different things and are treated differently.

To backup the .mdf and/or .ldf files, SQL Server must be offline or the database(s) must be detached.

To run BACKUP DATABASE or BACKUP LOG commands, the database(s) can be in use.


-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Ginka,

What exactly are you trying to accomplish with these backups? SQLBill is correct in that doing backups of the database (and Transaction Logs) is a completely different matter than backing up the .mdf and the .ldf files.

If all you want to do is get backups of the data that you can restore to given times, then just do regular database backups via Enterprise Manager or Query Analyzer or a scheduled T-SQL Job.

If you want to move the database to another machine for other purposes, you can either detach the database and copy the files or use Log Shipping or move a recent complete db backup.

The problem with backing up the .mdf & .ldf files is that you'll get a good backup of the database, but it won't be particularly current once you've got users back on the database.

So, again, can you share with us what you're trying to accomplish? That will make it easier for us to assist you.

Thanks,



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!