Just the basics, please

[emulator]

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

[Mayoff]

Retrospect uses standard Microsoft backup commands. These are not unique to Retrospect.

 

Google is your friend.

 

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

 

http://www.dbarecovery.com/backuptypes.html

 

http://www.google.com/search?client=safari&rls=en-us&q=SQL+backup+types&ie=UTF-8&oe=UTF-8

 

[emulator]

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!

[Mayoff]

Full is always best for all sql and exchange backups. Most people do full every night.

[emulator]

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?

[Mayoff]

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.

[emulator]

I posted on a MS SQL message board and will post back what I find here. Thank you!

[emulator]

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 April 17, 2008 by Guest

[Mayoff]

humm, the text in the Retrospect options sure does look similar....

[emulator]

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 April 18, 2008 by Guest

[MRIS]

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)

[emulator]

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.