Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

backup Database Task - transaction log

I am looking under backup database tasks in maintenance plans and notice the different backup types.  What is the purpose of Transaction Log?  Is that for backing up the transaction log? Do I need to be backing up the transaction log?

What is the purpose of the transaction log?
0
al4629740
Asked:
al4629740
  • 5
  • 5
  • 2
  • +2
6 Solutions
 
Tony303Commented:
Hi,

It is a complex issue you're asking about....
Here is an article herebwhich is long but should have all the answers for you....
http://www.experts-exchange.com/Database/MS-SQL-Server/SQL-Server-2005/A_657-Managing-the-Transaction-Log-for-the-Accidental-DBA.html
0
 
Tony303Commented:
Simply for you.

The transaction log records every transaction that occurs in the database.

The maintenance plan has the ability to backup the trans log.

If your database is in full recovery model the yes you need to backup the transaction log.
0
 
BembiCEOCommented:
SQL server is a transaction log driven database. That means, that each change in the database is first written into the transaction log. During idle times, the data is written back into the physical data store.

The sense of the transaction log is to have something like a protocoll of all commands, send to the database. As database backups only represent a backup of the state of the databse, when it is backed up, the transaction logs can make sure that you can recover the latest state if the database when an error happens.

Physical database and transactions logs are usually stored on differnet physical stores. If one store fails, you can can recover the physial store and replay all subsequent transactions to recover it to the latest state. Otherwise you would need a "on the fly" backup of the the physical database to make sure to be able to recover the latest state.

A maintenance plan has the option to backup the physical databse as well as transaction logs. If one part is still available, you are able to recover the latest state by either recover the database and replay the alive transaction logs, or the teansaction logs are lost and the physical databse is alive.

In usual scenarios, the databse is backed up i.e once a day and he transactions logs i.e every 4 hours. If the database crashed, an older backup + transacrion logs provide the latest state. If the logs crashes , the database is still alive and provide the latest state.

Means, if database abd transactions logs reside on independend storages, you are able to recover the latest state from backups as one of the two parts is still alive.

When the physical database is backed up, the transactions logs are cut off to the state of the saved database and all logs after the backup will remain active.

To your question:
Make sure, the database path and transaction logs are on two different storage systems. In the situation of failure, only one part is destroyed. Make backup of both, database and transactions logs. In the point of failure, you can recover the latest state by either recovering the datebase + alive transactions logs or by having the alive database and recover the latest transaction logs.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
al4629740Author Commented:
"on two different storage systems"  Are you talking about possibly backing up to two different externals?
0
 
al4629740Author Commented:
Tony,

What if I convert it to Simple, then do I need to backup the transaction log?
0
 
Steve WalesSenior Database AdministratorCommented:
If you convert it to Simple you don't need to backup the transaction log.

HOWEVER ... you lose the ability to restore to any point in time in the event of a failure.

With Transaction logs, you can restore a database usually right up to the point of failure in the event of a disaster.

In Simple mode you can only recover to the point of last backup.

Depends upon how important your data is to you :)

In Full Recovery mode, also, if you're not backing up your transaction log, it will grow forever and ever.

Some related information on this particular aspect of transaction logs are in this article.
0
 
al4629740Author Commented:
So in conclusion, it is better to do a FULL backup and then backup the transaction log.  By backing up the transaction log through Maintenance Plans, this will prevent the log from becoming enormous.

Correct?
0
 
Steve WalesSenior Database AdministratorCommented:
Seems correct.

You want to do a regular FULL backup and then periodic TLog backups.  

Example:

Full Daily
TLog hourly (or more frequently depending upon transaction volume, how critical your data is etc....)

Backing up the Transaction Log is what marks the space in the file able to be reused.
0
 
BembiCEOCommented:
"on two different storage systems" --> No...
Transactions logs and physical database should not reside on the same physical storage. If this storage crashes, databass AND transaction log are away...

Simple mode writes back the data immediately. This method doesn't allow to recover the latest state, only the latest backup.

Full mode is therefore more save, more reliable and also possibly a bit more efficient (due idle times can be used). If distributed over several physical drives even possibly faster.

Backup for full mode is backup of the physical database (mdf) AND backup of the transaction logs (ldf). Maintenance plan is o.k. Transaction log backup is more often (to keep them small) as pyhsical db backup.
0
 
Steve WalesSenior Database AdministratorCommented:
>>Backup for full mode is backup of the physical database (mdf) AND backup of the transaction logs (ldf).

This is incorrect.

Refer to the docs: http://technet.microsoft.com/en-us/library/ms186865.aspx

"During a full or differential database backup, SQL Server backs up enough of the transaction log to produce a consistent database when the backup is restored."

Full backup (BACKUP DATABASE) backs up the database (.mdf and .ndf's) and only enough of the TLogs to make a consistent image.  If you want transaction logs, you need to backup the logs separately with BACKUP LOG.
0
 
Anthony PerkinsCommented:
If you convert it to Simple you don't need to backup the transaction log.
Not to quibble, but if you convert to Simple Recovery Model you cannot backup the Transaction Log.
0
 
al4629740Author Commented:
So after all this discussion, which I appreciate.  I think if I setup a Full backup and a Log backup, I should be ok.

Correct?
0
 
Steve WalesSenior Database AdministratorCommented:
Yes - just be aware of the scheduling of times as indicated above.
0
 
al4629740Author Commented:
so for the logs I would have to specify to do a backup every 4 hours for example under Maintenance Plans?
0
 
Steve WalesSenior Database AdministratorCommented:
Correct - some interval that's relatively frequent.

Also, make sure when you're setting up your maintenance plans that you have a Cleanup task that deletes backups older than x number of days - otherwise they'll keep writing them out forever.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now