Question regarding .LDF and .MDF files and backup

Hello,
I have pretty basic question regarding databases on my MS SQL server.
There are .MDF and .LDF files. From what I can tell (and I may well be VERY
wrong here), the MDF file is the REAL data. The LDF file are changes yet to
be merged into the live data.
1 - What are the LDF files?
2 - Why are they so big? Do they never flush?
3 - When do "transactions" become part of the "data"?
4 - If I want to make a Full backup of a database can I make a full database
backup or do I need to make a logfiles backup also to get a "full backup".
The database is not written so often to so a full backup each night is
enough.
Thanks in advance.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
The transaction log.
> 2 - Why are they so big? Do they never flush?
They grow until you either backup the log, or set the database to Simple
recovery mode
> 3 - When do "transactions" become part of the "data"?
Immediately when the transaction commits
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
Set the database to Simple recovery mode, then backup the entire database
nightly... You will loose any transactions since the last full backup.
>
> Thanks in advance.
|||There is a section in BOL titled "Physical Database Architecture",
explaining the files and their usage. The section title "Backup/Restore
Architecture" will also be useful.
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.
|||Some information scattered in these articles about why log files are
large...
http://www.aspfaq.com/2446
http://www.aspfaq.com/2471
http://www.aspfaq.com/
(Reverse address to reply.)
"Magnus" <Magnus@.discussions.microsoft.com> wrote in message
news:C5C38AE4-1C1D-4B1F-BEB7-D22565A8FE8E@.microsoft.com...
> Hello,
> I have pretty basic question regarding databases on my MS SQL server.
> There are .MDF and .LDF files. From what I can tell (and I may well be
VERY
> wrong here), the MDF file is the REAL data. The LDF file are changes yet
to
> be merged into the live data.
> 1 - What are the LDF files?
> 2 - Why are they so big? Do they never flush?
> 3 - When do "transactions" become part of the "data"?
> 4 - If I want to make a Full backup of a database can I make a full
database
> backup or do I need to make a logfiles backup also to get a "full backup".
> The database is not written so often to so a full backup each night is
> enough.
>
> Thanks in advance.