Database backups

I have some confusion about backing up databases and transaction logs.
When I do a complete database backup it backs up the db and the log, but
when I have it set to clear out inactive entries in the log it does not.
When I do a log backup and set it to clear out inactive entries it does do
that. My question is then if I do complete database backups all the time and
the log is not cleared for a long time and I do a restore and then play back
the log from a long time ago will I corrupt the database? How should I do
backups. Should I backup the log first clear it then backup the full
database or db first then log? I am confused. Thanks.
Tom,

> How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
- Full db backup (every night or once a week, depend the size of your db and
the time window you have for doing it)
- Differential backup (one daily or two, depend the activity. Let us say at
noon. this will save time restoring the db because you do not have to restore
all transaction log backups from the last full one, instead you restore the
full backup, the last diff and the trans log backups from the last diff
including a backup of the active one)
- Log backups between full / diff (from full to diff and from diff to full,
or from diff to diff if there is not a full between them, or between full to
full if not diff), as many as you need to avoid it from growing and be save
in case somthing bad happen and we can not backup active one.
Here you will find some good articles about backup and restore.
http://www.karaszi.com/SQLServer/articles.asp
AMB
"Tom Reis" wrote:

> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>
>
|||I forgot to mention that full and diff backups do not truncate the
transaction log, just transaction log backup does this when recovery model is
bulk-logged or full. The procedure for simple recovery model is different.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Tom,
>
> - Full db backup (every night or once a week, depend the size of your db and
> the time window you have for doing it)
> - Differential backup (one daily or two, depend the activity. Let us say at
> noon. this will save time restoring the db because you do not have to restore
> all transaction log backups from the last full one, instead you restore the
> full backup, the last diff and the trans log backups from the last diff
> including a backup of the active one)
> - Log backups between full / diff (from full to diff and from diff to full,
> or from diff to diff if there is not a full between them, or between full to
> full if not diff), as many as you need to avoid it from growing and be save
> in case somthing bad happen and we can not backup active one.
> Here you will find some good articles about backup and restore.
> http://www.karaszi.com/SQLServer/articles.asp
> AMB
>
> "Tom Reis" wrote:
|||> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
The "clear entries..." is a terribly bad designed way to expose the NO_TRUNCATE option for the
BACKUP LOG command. It has no meaning for BACKUP DATABASE. The checkbox should be disabled for
BACKUP DATABASE. Run a profiler trace and you will se that EM generates the exact same BACKUP
DATABASE command whether or not the checkbox is checked.

> When I do a log backup and set it to clear out inactive entries it does do
> that.
Yes, this is a normal log backup. Checking this option should *only* be done of you have a corrupt
database and want to back the log. Read about the NO_TRUNCATE option and also
http://www.karaszi.com/SQLServer/inf..._truncate.asp.

> My question is then if I do complete database backups all the time and
> the log is not cleared for a long time
Why would you want to do that? A database backup is a snapshot in time. If you don't do log backups,
then run in simple recovery mode.

> How should I do
> backups.
Either to db backup say every day and log backups say every hour (or every ten minutes).
Or if you don't want to do log backups, put the db in simple recovery mode and only do db backups.
It is not very complicated. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Reis" <reistom@.cdnet.cod.edu> wrote in message news:OKGvIFMqFHA.820@.TK2MSFTNGP09.phx.gbl...
> I have some confusion about backing up databases and transaction logs.
> When I do a complete database backup it backs up the db and the log, but
> when I have it set to clear out inactive entries in the log it does not.
> When I do a log backup and set it to clear out inactive entries it does do
> that. My question is then if I do complete database backups all the time and
> the log is not cleared for a long time and I do a restore and then play back
> the log from a long time ago will I corrupt the database? How should I do
> backups. Should I backup the log first clear it then backup the full
> database or db first then log? I am confused. Thanks.
>