Backups - Transaction Log and Differential

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Backups - Transaction Log and Differential

Backups - Transaction Log and Differential

Rate Topic

Display Mode

Topic Options

Author

Message

kramaswamy

Posted Tuesday, September 18, 2012 12:58 PM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:53 PM
Points: 785, Visits: 1,534

Hi all,

I'm a bit confused regarding the different modes of backup.

If I understand correctly, a FULL backup takes a backup of the entire database, and then takes a backup of the log file, and then finally resets the log file, and starts fresh from there.

A Differential backup takes only the changes that have been made, to the log file and the database file, and backs those up, such that restoring a differential backup involves first restoring the full backup, and then restoring the differential from there.

So, if I'm right about that, what exactly does a transaction log backup do?

Or am I mistaken regarding the differential backup, and that the differential backup *only* backs up the database file itself (the MDF file), while the transaction log backup is necessary for backing up the log file (the LDF file)? In which case *both* the differential AND transaction log backups are necessary to restore a database, and both need to be done at the same time?

Post #1360967

Lynn Pettis

Posted Tuesday, September 18, 2012 1:01 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 21,635, Visits: 27,495

kramaswamy (9/18/2012)

No, a full back does nothing more than back up enough of the transaction log to ensure a consistant database. A differential backup backs up all changes since the latest full backup. Only transaction log backups will backup the transaction log and mark the VLF's as available if they have no active transactions.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1360970

Lynn Pettis

Posted Tuesday, September 18, 2012 1:03 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:52 PM
Points: 21,635, Visits: 27,495

This article should help with understanding transaction log backups:

http://www.sqlservercentral.com/articles/Administration/64582/

I would also recommend reading about these in Books Online, the SQL Server Help System.

Cool

Lynn Pettis

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1360971

Jayanth_Kurup

Posted Tuesday, September 18, 2012 1:07 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008

the difference is point in time recovery , if ur using sql 2012 , when restoring check out the timeline button on the right you will be able to see the difference between restoring a diff backup and a t log backup.

Jayanth Kurup

Post #1360973

kramaswamy

Posted Tuesday, September 18, 2012 1:23 PM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:53 PM
Points: 785, Visits: 1,534

Thanks for the link, Lynn. That clarifies a lot.

If I understood correctly, the fact that I had not enabled transaction log backups on my system meant that A), the system was effectively working as a simple recovery model, since it could only restore to the previous full + differential backup combo, and B) the log file would continue to grow indefinitely.

I am still a bit unclear on one thing though - Is there any point in keeping a history of transaction log backup files? If I understand correctly, if I have a full backup on Sunday and then transaction log backups every day until the following Sunday when a new full backup is made, I won't need to have a history of those transaction log backups, since the log recovery chain will go:

Sunday Full Backup -> Monday - Saturday Transaction Log Backup -> Sunday Full Backup.

Post #1360989

Jayanth_Kurup

Posted Tuesday, September 18, 2012 1:28 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 6:43 AM
Points: 1,785, Visits: 1,008

your corect , once the full backup is taken the whole process starts all over again ,any log file prior to that are not required, however if in the second week you decide you need to revert to a point in time in the first then these files are the only way.

Jayanth Kurup

Post #1360995

GilaMonster

Posted Tuesday, September 18, 2012 1:49 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:18 PM
Points: 37,744, Visits: 30,025

I like to keep log backups going back 2 full backups at least. That way, if, with the backups you listed, I had to restore to Monday and the sunday full backup was corrupt, missing or unrestorable, I could go to the previous full backup and restore a week and a bit of log backups. More options for recovery

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1361006

kramaswamy

Posted Thursday, September 20, 2012 8:09 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:53 PM
Points: 785, Visits: 1,534

Hey -

I think that it's possible that my backup chain may have been corrupted by some transaction logs being removed by other people. The server is working perfectly fine though.

If I make a full backup of the database, will the backup chain be okay as of that full backup? I know that I won't be able to restore to before that backup if the chain was corrupted before it, but going forward will everything be okay?

Post #1362002

durai nagarajan

Posted Thursday, September 20, 2012 8:25 AM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 23, 2013 2:05 AM
Points: 856, Visits: 2,115

kramaswamy (9/20/2012)

Keeping a extra days fullbackup and Tlog will help incase of server and latest db corruption or missing.

Full backup is like starting fresh so log backup will be ok after that.

Regards
Durai Nagarajan

Post #1362022

aroma01

Posted Saturday, September 22, 2012 9:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, September 22, 2012 12:53 PM
Points: 2, Visits: 24

after backup trans.. log will going to be empty,,
any way of backup.
recomended is keep list one dayly separeted from server room.

once can houpemm
if you have replication,to other servers,
works 24/7(they opening trasaccion log)
your .mdf file will be so big.
way is make that replication not work,,list one hour,,after backup,
make tame for database empty non transact... logs.

by
aleksandar

Post #1363117

« Prev Topic | Next Topic »

Permissions