Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Backups - Transaction Log and Differential Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 12:58 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 11, 2014 10:19 AM
Points: 815, Visits: 1,641
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
Posted Tuesday, September 18, 2012 1:01 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:21 PM
Points: 22,506, Visits: 30,219
kramaswamy (9/18/2012)
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?


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.



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
Posted Tuesday, September 18, 2012 1:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:21 PM
Points: 22,506, Visits: 30,219
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.



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 #1360971
Posted Tuesday, September 18, 2012 1:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 11:33 PM
Points: 1,789, Visits: 1,013
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
Posted Tuesday, September 18, 2012 1:23 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 11, 2014 10:19 AM
Points: 815, Visits: 1,641
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
Posted Tuesday, September 18, 2012 1:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 11:33 PM
Points: 1,789, Visits: 1,013
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
Posted Tuesday, September 18, 2012 1:49 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:52 AM
Points: 41,530, Visits: 34,446
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
Posted Thursday, September 20, 2012 8:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 11, 2014 10:19 AM
Points: 815, Visits: 1,641
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
Posted Thursday, September 20, 2012 8:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 5:48 AM
Points: 1,048, Visits: 2,646
kramaswamy (9/20/2012)
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?


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
Posted Saturday, September 22, 2012 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse