Solved

backup Database Task - transaction log

Posted on 2014-04-28
15
346 Views
Last Modified: 2014-04-29
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
Comment
Question by:al4629740
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 12

Expert Comment

by:Tony303
ID: 40028149
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
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 84 total points
ID: 40028158
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
 
LVL 35

Assisted Solution

by:Bembi
Bembi earned 83 total points
ID: 40028193
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
 

Author Comment

by:al4629740
ID: 40028240
"on two different storage systems"  Are you talking about possibly backing up to two different externals?
0
 

Author Comment

by:al4629740
ID: 40028274
Tony,

What if I convert it to Simple, then do I need to backup the transaction log?
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 333 total points
ID: 40028318
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
 

Author Comment

by:al4629740
ID: 40028430
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 333 total points
ID: 40028486
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
 
LVL 35

Expert Comment

by:Bembi
ID: 40028924
"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
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 333 total points
ID: 40029340
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40029360
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
 

Author Comment

by:al4629740
ID: 40030781
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
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40030815
Yes - just be aware of the scheduling of times as indicated above.
0
 

Author Comment

by:al4629740
ID: 40030949
so for the logs I would have to specify to do a backup every 4 hours for example under Maintenance Plans?
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 333 total points
ID: 40030958
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now