Full backups and transaction log backups

Full backups and transaction log backups

Post by Travis Rosso » Mon, 10 Apr 2000 04:00:00



Hello all!  Sorry for my ignorance here, but I probably have a stupid
question.  I am using HP Omniback to backup SQL 7.0 on an NT cluster.  I am
implementing full backups at midnight and transaction log backups every 6
hours.  There is an option in Omniback where I can truncate the transaction
log after a full backup.  If I do this, will I be able to utilize my
transaction logs to do a point in time restore?  I have not found
documentation on this, but when you use to do a full database backup using
SQL Enterprise manager, does it truncate the transaction log by default?

Thanks in advance,
Travis

 
 
 

Full backups and transaction log backups

Post by Neil Boyl » Tue, 11 Apr 2000 04:00:00


Doing transaction log backups will keep your transaction log from filling
up, but full database dumps will not.

You cannot truncate transaction logs if you want to use transaction log
backups and rollforward recovery.

--

Neil Boyle
Impetus Network Systems DBA's Handbook, A free SQL Server guide at
http://www.impetus-sql.co.uk/


>Hello all!  Sorry for my ignorance here, but I probably have a stupid
>question.  I am using HP Omniback to backup SQL 7.0 on an NT cluster.  I am
>implementing full backups at midnight and transaction log backups every 6
>hours.  There is an option in Omniback where I can truncate the transaction
>log after a full backup.  If I do this, will I be able to utilize my
>transaction logs to do a point in time restore?  I have not found
>documentation on this, but when you use to do a full database backup using
>SQL Enterprise manager, does it truncate the transaction log by default?

>Thanks in advance,
>Travis


 
 
 

Full backups and transaction log backups

Post by Tibor Karasz » Tue, 11 Apr 2000 04:00:00


Travis,

Your log backups are worthless, If you truncate the log _after_ the db
backup.
I don't reccomend truncating the log before db backup either. If you keep
the log, you can survive a corrupt db backup (go back to the previous)...

--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.


Quote:> Hello all!  Sorry for my ignorance here, but I probably have a stupid
> question.  I am using HP Omniback to backup SQL 7.0 on an NT cluster.  I
am
> implementing full backups at midnight and transaction log backups every 6
> hours.  There is an option in Omniback where I can truncate the
transaction
> log after a full backup.  If I do this, will I be able to utilize my
> transaction logs to do a point in time restore?  I have not found
> documentation on this, but when you use to do a full database backup using
> SQL Enterprise manager, does it truncate the transaction log by default?

> Thanks in advance,
> Travis

 
 
 

Full backups and transaction log backups

Post by C. E. Buttle » Tue, 11 Apr 2000 04:00:00


You cannot perform a transaction log backup after a truncate log dump UNLESS
you do a database dump after the truncate log dump.  If anything, do a
database dump once a day, then transaction log dumps as frequently as you
like during the day.  Don't truncate unless you have to.  Increasing
frequency of transaction log dumps will normally do the same thing.

Quote:> Hello all!  Sorry for my ignorance here, but I probably have a stupid
> question.  I am using HP Omniback to backup SQL 7.0 on an NT cluster.  I
am
> implementing full backups at midnight and transaction log backups every 6
> hours.  There is an option in Omniback where I can truncate the
transaction
> log after a full backup.  If I do this, will I be able to utilize my
> transaction logs to do a point in time restore?  I have not found
> documentation on this, but when you use to do a full database backup using
> SQL Enterprise manager, does it truncate the transaction log by default?

> Thanks in advance,
> Travis

 
 
 

Full backups and transaction log backups

Post by Travis Rosso » Tue, 11 Apr 2000 04:00:00


Can you give me a example of a backup strategy using a full backup and
transaction log backups?  I am a little confused by when to truncate the
transaction log.  As an example:
  midnight: full backup
  6AM: transacation log backup
  noon: transaction log backup
  6PM: transaction log backup

When should I truncate the transaction log if I want to do a point in time
restore?  I know that when I have a db failure, I would backup the
transaction log without truncting the transaction log.


> You cannot perform a transaction log backup after a truncate log dump
UNLESS
> you do a database dump after the truncate log dump.  If anything, do a
> database dump once a day, then transaction log dumps as frequently as you
> like during the day.  Don't truncate unless you have to.  Increasing
> frequency of transaction log dumps will normally do the same thing.


> > Hello all!  Sorry for my ignorance here, but I probably have a stupid
> > question.  I am using HP Omniback to backup SQL 7.0 on an NT cluster.  I
> am
> > implementing full backups at midnight and transaction log backups every
6
> > hours.  There is an option in Omniback where I can truncate the
> transaction
> > log after a full backup.  If I do this, will I be able to utilize my
> > transaction logs to do a point in time restore?  I have not found
> > documentation on this, but when you use to do a full database backup
using
> > SQL Enterprise manager, does it truncate the transaction log by default?

> > Thanks in advance,
> > Travis

 
 
 

Full backups and transaction log backups

Post by Tibor Karasz » Tue, 11 Apr 2000 04:00:00


Travis,

The log _is_ truncated when you perform BACKUP LOG. That is the normal way
of "removing log-records" from the transaction log.

--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.


> Can you give me a example of a backup strategy using a full backup and
> transaction log backups?  I am a little confused by when to truncate the
> transaction log.  As an example:
>   midnight: full backup
>   6AM: transacation log backup
>   noon: transaction log backup
>   6PM: transaction log backup

> When should I truncate the transaction log if I want to do a point in time
> restore?  I know that when I have a db failure, I would backup the
> transaction log without truncting the transaction log.


> > You cannot perform a transaction log backup after a truncate log dump
> UNLESS
> > you do a database dump after the truncate log dump.  If anything, do a
> > database dump once a day, then transaction log dumps as frequently as
you
> > like during the day.  Don't truncate unless you have to.  Increasing
> > frequency of transaction log dumps will normally do the same thing.


> > > Hello all!  Sorry for my ignorance here, but I probably have a stupid
> > > question.  I am using HP Omniback to backup SQL 7.0 on an NT cluster.
I
> > am
> > > implementing full backups at midnight and transaction log backups
every
> 6
> > > hours.  There is an option in Omniback where I can truncate the
> > transaction
> > > log after a full backup.  If I do this, will I be able to utilize my
> > > transaction logs to do a point in time restore?  I have not found
> > > documentation on this, but when you use to do a full database backup
> using
> > > SQL Enterprise manager, does it truncate the transaction log by
default?

> > > Thanks in advance,
> > > Travis

 
 
 

Full backups and transaction log backups

Post by Travis Rosso » Tue, 11 Apr 2000 04:00:00


Just so that I am straight on this.
  Midnight: full backup (do not truncate transaction log)
  6AM: transaction log backup (transaction log truncated)
  noon: transaction log backup (transacation log truncated)
  6PM: transaction log backup (transaction log truncated)
At DB failure, run transaction log backup without truncating transaction
log.  Then use latest full backup and transaction logs to do a point in time
restore.

Thanks,
Travis

> Travis,

> The log _is_ truncated when you perform BACKUP LOG. That is the normal way
> of "removing log-records" from the transaction log.

> --
> Tibor Karaszi, Cornerstone Sweden AB
> MCDBA, MCSE, MCSD, MCT, SQL Server MVP
> Please reply to the newsgroup only, not by email.



> > Can you give me a example of a backup strategy using a full backup and
> > transaction log backups?  I am a little confused by when to truncate the
> > transaction log.  As an example:
> >   midnight: full backup
> >   6AM: transacation log backup
> >   noon: transaction log backup
> >   6PM: transaction log backup

> > When should I truncate the transaction log if I want to do a point in
time
> > restore?  I know that when I have a db failure, I would backup the
> > transaction log without truncting the transaction log.


> > > You cannot perform a transaction log backup after a truncate log dump
> > UNLESS
> > > you do a database dump after the truncate log dump.  If anything, do a
> > > database dump once a day, then transaction log dumps as frequently as
> you
> > > like during the day.  Don't truncate unless you have to.  Increasing
> > > frequency of transaction log dumps will normally do the same thing.


> > > > Hello all!  Sorry for my ignorance here, but I probably have a
stupid
> > > > question.  I am using HP Omniback to backup SQL 7.0 on an NT
cluster.
> I
> > > am
> > > > implementing full backups at midnight and transaction log backups
> every
> > 6
> > > > hours.  There is an option in Omniback where I can truncate the
> > > transaction
> > > > log after a full backup.  If I do this, will I be able to utilize my
> > > > transaction logs to do a point in time restore?  I have not found
> > > > documentation on this, but when you use to do a full database backup
> > using
> > > > SQL Enterprise manager, does it truncate the transaction log by
> default?

> > > > Thanks in advance,
> > > > Travis

 
 
 

Full backups and transaction log backups

Post by Neil Boyl » Tue, 11 Apr 2000 04:00:00


Quote:> At DB failure, run transaction log backup without truncating transaction
> log.  Then use latest full backup and transaction logs to do a point in
time
> restore.

At db failure you need a restore, by then it's too late for a backup :-)

If your database fails at 7:30 p.m., you can only roll forward as far as
6:00 p.m. because that's the last backup you took before the failure - you
start by restoring from the latest full database dump, then applying the
transaction log dumps in sequence.

You can increase your "protection" by taking transaction log backups more
often, and right up to "just before" your next full database dump - which is
what Tibor was getting at earlier.

Don't forget that your backup files need to be *off* your server - no use
backing up to the same machine that your databases live on if the whole
machine goes kaput.

--

Neil Boyle
Impetus Network Systems DBA's Handbook, A free SQL Server guide at
http://www.impetus-sql.co.uk/

 
 
 

Full backups and transaction log backups

Post by Tibor Karasz » Wed, 12 Apr 2000 04:00:00


Exactly!!!

(Note that you cannot do log backup if you've lost the mdf file. Some dba's
use file groups and "steer off" data from the mdf file this way. See Wayne
Snyder's article in Oct SQLMag for elaboration...)

--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.


Quote:> Just so that I am straight on this.
>   Midnight: full backup (do not truncate transaction log)
>   6AM: transaction log backup (transaction log truncated)
>   noon: transaction log backup (transacation log truncated)
>   6PM: transaction log backup (transaction log truncated)
> At DB failure, run transaction log backup without truncating transaction
> log.  Then use latest full backup and transaction logs to do a point in
time
> restore.

> Thanks,
> Travis

 
 
 

Full backups and transaction log backups

Post by Tibor Karasz » Wed, 12 Apr 2000 04:00:00


Neil,

Quote:> At db failure you need a restore, by then it's too late for a backup :-)

Depend on what kind of failure we're looking at. If NT, SQL Server the log
files and the mdf file is alive, we can do a log backup here
(NO_TRUNCATE)...
--
Tibor Karaszi, Cornerstone Sweden AB
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Please reply to the newsgroup only, not by email.


<snip>

 
 
 

1. Backup from filegroup without transaction log backups

I have a mssql 7.0 database of 2 GB (it will probably grow to 5 GB). In
this database I have approximately 130 tables. Every night I make a
full backup. Sometimes I get called that the data updated in an table
over the last month is totally wrong. The table needs to be recovered
to the state it was in the previous month.

In mssql 6.5 I restored just that one table. In mssql 7.0 this is not
possible any more. The server is not big enough to contain 2 databases
of 5 GB.

I have the following solution. I know which tables can cause problems.
I want to put these tables on there own file group. If I have to
restore a table I restore that filegroup. However I dont have the
transaction log backups from the previous month.

Question will the database be consistent without the transaction log
backups?

Sent via Deja.com http://www.deja.com/
Before you buy.

2. float display: QA vs. EM

3. Transaction log, full backup a must?

4. SQLMail work with other mail server?

5. Transaction Log full after DB Backup

6. run out of locks

7. Question about Transaction log backup with Backup Exec

8. Referential integrity broken after repairing a database with REPAIR_ALLOW_DATA_LOSS option?

9. SQL DB Backup - transaction log backup?

10. full database backup and the transaction log

11. Can Transaction Log Backup be Initiated If Some % Full

12. Big Transaction log after full backup

13. Backup from filegroup without transaction log backups