Jump to content
emulator

Just the basics, please

Recommended Posts

Hi all...

 

Please forgive the elementary nature of my question, but could someone please explain the differences between these two database backup types:

 

1. Log backup

2. Log backup no truncate

 

I have scoured EMC's site, and I even resorted to reading the manual ;-) and I can't find anything that fully explains what the difference is.

 

Thanks in advance!

 

Marc

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

Hey Robin...

 

Thanks for the quick reply. But what I can't seem to find is which is the better strategy: truncate or no truncate. Truncate seems to reduce the size of the transaction log, which would make sense to save disk space. However, there appears to be some risk doing this, as we are removing data from the transaction logs.

 

What would you recommend as a general best practice? Here is what we are currently doing:

 

1. Full backup every night

2. Transaction log backup, no truncate

 

Would adding a daily truncate be advantageous?

 

Thanks again!

Share this post


Link to post
Share on other sites

We used to do just a dailly, full backup. However, when we needed to restore the database PLUS the transaction logs, we were kinda in a pickle. That's why we started backing up the transaction logs. (I've been reading on the net that it's a good practice to back up transaction logs every hour).

 

So I'm back to my original question: which is best - the log with or without truncation?

Share this post


Link to post
Share on other sites

Don't think I am passing the buck, but the best people to ask is Microsoft Support, since these are Microsoft commands. I just don't know the answer.

Share this post


Link to post
Share on other sites

I found this in the, well, ummm, BackupExec manual online. It does a fairly good job of explaining what the two methods are, and explains when to use one vs. the other. Maybe EMC could update the documentation for Retro to better explain database procedures:

 

â—† Log - Back up transaction log. Select this option to back up

only the data contained in the transaction log; it does not back

up database data. After the transaction log is backed up,

committed transactions are removed (truncated).

Select this option to be able to select No recover - Place

database in loading state or Standby - Place database in

standby state under Enable advanced log backup options.

 

â—† Log No Truncate - Back up transaction log - no truncate. Select

this method only when the database is corrupted or database

files are missing. Since the Log No Truncate method does not

access the database, you can still back up transactions that you

may not be able to access otherwise when the database is in

this state. You can then use this transaction log backup along

with the database backup and any previous transaction log

backups to restore the database to the point at which it failed;

however, any uncommitted transactions are rolled back. The

Log No Truncate method does not remove committed

transactions after the log is backed up.

Edited by Guest

Share this post


Link to post
Share on other sites

Touche!

 

In any case, I got an answer on Microsoft's forums:

 

"I'd limit the use of NO_TRUNCATE to when you need to recover an inaccessible database and not as part of my standard backup strategy. Remember, NO_TRUNCATE doesn't remove inactive entries from the log, so it will continue to grow and could end up causing you problems (tlog or disk filling up)."

 

This is pretty much what the read_me tip in Retrospect (per the above screenshot) eludes to. I'm going to start the following backup scheme:

 

1. Full backup once a week

2. Differential backup once a day

3. Transaction log backup WITH truncation once an hour

 

Marc

Edited by Guest

Share this post


Link to post
Share on other sites

Does a "Full Backup" on the retrospect options screen imply that it will truncate the log as part of this full-backup operation?

 

Or does an explicit additional log backup need to be done to keep the .ldf files trimmed?

 

So which way is it?

 

option A: 2 backups of each SQL server each night, one full, one log, so the log truncates?

 

option B: 1 full backup of the SQL server (logs get truncated automatically)

Share this post


Link to post
Share on other sites
Does a "Full Backup" on the retrospect options screen imply that it will truncate the log as part of this full-backup operation?

 

Or does an explicit additional log backup need to be done to keep the .ldf files trimmed

 

The way that I understand it is that a Full SQL backup does not truncate the transactional log. All it does is back up the entire database. In order to truncate committed entries in the transactional log is to run a LOG backup.

 

So which way is it?

 

option A: 2 backups of each SQL server each night, one full, one log, so the log truncates?

 

option B: 1 full backup of the SQL server (logs get truncated automatically)

 

It's option A.

 

On a side note, I have since altered the way that we back up, refining the process a bit more. Here's what we do. I hope that this helps:

 

๠Recycle, Full SQL backups are run on all Microsoft SQL databases once a week.

๠Recycle, Full SQL backups are performed on all master, msdb, and model databases once a week.

๠Normal, Differential backups are performed on Full Recovery Model databases every three hours.

๠Normal, Log backups are performed on Full Recovery Model databases every hour. This automatically truncates the transactional log.

 

A few notes that you need to remember:

 

1. Only databases with the FULL RECOVERY MODEL may have Differential and/or Log Backups.

2. The Master, msdb, and model databases are system databases that are installed by default on a MS SQL server. They are required for the database to function properly. This is why we back them up. More information on this is available here:

 

http://sanssql.blogspot.com/2008/06/what-is-basic-functions-for-master-msdb_12.html

 

The above backup routine has worked pretty well for us. We've already had to do a restore of a critical database, and it worked great.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×