Backups of All DB's and Transaction logs in SQL 2000

[Welshbird]

HELP!

I have set up a maintenance plan to backup all database, which works fine, but the plan to back up all transaction logs fails every day.

If I run a checkpoint before the database backup, does it really matter if I don't backup the logs anyway? - Or is there something missing in my plan to backup the transaction logs.

I'd appreciate any ideas, having trouble here.

Thanks in advance!


Fi.

 

[tlbroadbent]

Do you see an error message when the transaction log backups fail? Could you post it so we will have a little more info to work with? Also tell us what the schedule for your backups is. For example, how long after the full backup does the transaction backup run? What is the frequency of the transaction backups?

Some possible causes of transaction log backup failure:

1- Select into, bulk copy and some DTS operations create non-logged transactions that invalidate log backups so they fail.

2- Truncate log on checkpoint can invalidate transaction logs so the backup will fail.

Terry

http://members.home.net/tlbroadbent/index.htm

 

[Welshbird]

The only message I get is via email:

JOB RUN: 'Transaction Log Backup Job for DB Maintenance Plan
'All_Logs_Backup'' was run on 20/04/2001 at 01:00:00
DURATION: 0 hours, 8 minutes, 24 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by Schedule 17 (Schedule 1).
The last step to run was step 1 (Step 1).

THe DB backup runs an hour before this - I think it is probably that a checkpoint runs before the transaction log backup, so I think I'm okay to not backup the logs......

What do you think?
Thanks for your help!

Fi

Fi.

 

[tlbroadbent]

Have you examined the history the transaction log backups? Use Enterprise Manager to look at the history and see if there is any additional information.

The Job History dialog box has a check box - Show step details - in the upper right hand corner. Check that on and then view the Job History step details. You may find a more detailed error message.

Also, the SQL error logs may have an entry for the error. For example, we get the following error when a database has had some sort of bulk-copy operation.

Cannot allow BACKUP LOG because file 'dbname' has been subjected to nonlogged updates and cannot be rolled forward. Perform a full database, or differential database, backup.

You mention that think it may be OK to not backup the logs. Each installation of SQL Server is different but if you have any kind of transactional updates to your database during the day, you should do transaction log backups.

Typically we backup a database once a day and do transaction backups at regular intervals through the day, which allows data recovery right up to the last log backup. At most we could lose 15 to 60 minutes of updates depending on the log backup frequency. We determine the frequency by the volume of updates and how much data loss we can tolerate.

We do not allow "truncate log on checkpoint" on any production database that has transactions. Truncating the log simply throws away the transactions and makes us vulnerable to data loss back to the point of the last full or differential backup.

I recommend the following articles on backups.

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9629

http://gethelp.devx.com/techtips/thesqlpro/10min/10min0101/10min0101.asp

http://www.mssqlserver.com/backup/overview_p1.asp

The last article is lengthy.
Terry

http://members.home.net/tlbroadbent/index.htm