Hello Group,

I am quite new with SQL 2000 so please forgive my simple questions.

I assume doing a full/complete backup is the easiest and most reliable way
to backup for a small SQL database (<500MB). My question is when I choose
the full backup option, does that include backing up the log files? I am
quite confused for usually full or complete backup means just that, but I
have read somewhere that log files must still be backed up separately.

Can anyone clarify this for me? Is there an option to backup the database
and logfiles in one shot?

Thank you for any help,
Bryan

Report this post to a moderator

A full backup is "self contained". But a db backup will not truncate old log records from the
log file. You do this by either do backup of log or set the recovery more to simple.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver


"Bryan" <bdoviack at hotmail dot com> wrote in message news:OEVPGxQeCHA.1736@tkmsftngp11...
> Hello Group,
>
> I am quite new with SQL 2000 so please forgive my simple questions.
>
> I assume doing a full/complete backup is the easiest and most reliable way
> to backup for a small SQL database (<500MB). My question is when I choose
> the full backup option, does that include backing up the log files? I am
> quite confused for usually full or complete backup means just that, but I
> have read somewhere that log files must still be backed up separately.
>
> Can anyone clarify this for me? Is there an option to backup the database
> and logfiles in one shot?
>
> Thank you for any help,
> Bryan
>
>
>
>

Report this post to a moderator

Yes, a full database backup includes the log as well. However, a full
backup does not truncate the log and this might be the source of your
confusion.

If you are running in the FULL or BULK LOGGED recovery model, you need
to periodically backup the log with BACKUP LOG so that you have a copy
of the committed transactions for recovery purposes. This also
truncates the log. The log file will grow indefinitely if you don't
backup the log periodically.

You don't need to backup the log separately if you run the SIMPLE
recovery model because the log is truncated automatically. Your only
recovery option in this mode is to restore from database backup.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"Bryan" <bdoviack at hotmail dot com> wrote in message
news:OEVPGxQeCHA.1736@tkmsftngp11...
> Hello Group,
>
> I am quite new with SQL 2000 so please forgive my simple questions.
>
> I assume doing a full/complete backup is the easiest and most reliable
way
> to backup for a small SQL database (<500MB). My question is when I
choose
> the full backup option, does that include backing up the log files? I
am
> quite confused for usually full or complete backup means just that,
but I
> have read somewhere that log files must still be backed up separately.
>
> Can anyone clarify this for me? Is there an option to backup the
database
> and logfiles in one shot?
>
> Thank you for any help,
> Bryan
>
>
>
>

Report this post to a moderator

In addition to the other's comments:

The log is used as a record for transactions that occur throughout the
normal operation of the database. If you're not in simple recovery mode
(SQL 2000) then the transaction log is creating a record of all the
transactions that are occuring until it is backed up. In fact, these
transactions are written to the transaction log before they are commited in
the database --usually in memory. This allows the database to be rolled
forward or rolled back. Rolling back is the process of restoring a backup
and then subsequent transaction logs up to a point before an error was
introduced. For example, if you are running a batch process at midnight and
know that it failed last night, you can roll the database back to the point
before the batch processing. This is done through point in time recovery or
naming a transaction (Books on line has great info on these). If you're in
simple recovery mode, the transactions are still recorded in the transaction
log but the log is periodically emptied once the transactions that have
completed are written to the datafiles on disk. Recall that I said a
transaction is usually completed --also known as being committed-- in
memory which creates a problem because a server failure can't capture what
is in memory. That's where the log again comes into action; when the server
comes back up the transaction log is read (recall all transactions are
written here first before being committed.) and then rolled forward in the
datafiles on disk. Check out the sql error log for your servers and note
the entries that detail starting up --you'll usually see some transactions
being rolled forward or back.

Regards




"Bryan" <bdoviack at hotmail dot com> wrote in message
news:OEVPGxQeCHA.1736@tkmsftngp11...
> Hello Group,
>
> I am quite new with SQL 2000 so please forgive my simple questions.
>
> I assume doing a full/complete backup is the easiest and most reliable way
> to backup for a small SQL database (<500MB). My question is when I choose
> the full backup option, does that include backing up the log files? I am
> quite confused for usually full or complete backup means just that, but I
> have read somewhere that log files must still be backed up separately.
>
> Can anyone clarify this for me? Is there an option to backup the database
> and logfiles in one shot?
>
> Thank you for any help,
> Bryan
>
>
>
>

Report this post to a moderator