Database backup strategy questions....

I have been reading about the SQL Server transaction log and I'm still
confused about a lot of things. First of all, what is this file
actually used for? In the past when backing up my database, I only
backup and restore the actual database and skip the log file and this
has worked just fine in getting all my data back. Why do I have to
backup the log file and when would I ever use it?
I am having a problem now becuase my log file is over 4 GB for a 50 MB
database and I'm not sure why. My application is a POS system where
hundreds of transactions are entered daily and backups are done each
night of the database but not the log. I can restore the database just
fine without the log file, what is the advantage of backing up the log
file also? I can't backup that much data so I'll need to make that
much smaller if I did back it up. Based on my needs what is the best
backup strategy and what would I lose by not backing up the log?
Thanks.Ray,
When you restore the database file from a Full backup it actually restores
the log file along with it. The Log file holds the transactional
information to ensure consistency within the db and can not be used without
it. The log backups allow you to potentially restore to a particular point
in time under thright conditions. If all you care about is being able to
restore to the last Full backup (potentially loosing all transactions since
that last backup) you should place the database in SIMPLE recovery mode and
it will automatically truncate the log and not grow. See here for more
details:
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly SQL MVP
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0401131950.120fda92@.posting.google.com...
> I have been reading about the SQL Server transaction log and I'm still
> confused about a lot of things. First of all, what is this file
> actually used for? In the past when backing up my database, I only
> backup and restore the actual database and skip the log file and this
> has worked just fine in getting all my data back. Why do I have to
> backup the log file and when would I ever use it?
> I am having a problem now becuase my log file is over 4 GB for a 50 MB
> database and I'm not sure why. My application is a POS system where
> hundreds of transactions are entered daily and backups are done each
> night of the database but not the log. I can restore the database just
> fine without the log file, what is the advantage of backing up the log
> file also? I can't backup that much data so I'll need to make that
> much smaller if I did back it up. Based on my needs what is the best
> backup strategy and what would I lose by not backing up the log?
> Thanks.