Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

backup Database Task - transaction log

Posted on 2014-04-28
15
Medium Priority
?
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 336 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 332 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 1332 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
 
LVL 23

Assisted Solution

by:Steve Wales
Steve Wales earned 1332 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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 1332 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 23

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 23

Accepted Solution

by:
Steve Wales earned 1332 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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