September 18, 2012 at 12:58 pm
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?
September 18, 2012 at 1:01 pm
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.
September 18, 2012 at 1:03 pm
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.
September 18, 2012 at 1:07 pm
September 18, 2012 at 1:23 pm
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.
September 18, 2012 at 1:28 pm
September 18, 2012 at 1:49 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 20, 2012 at 8:09 am
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?
September 20, 2012 at 8:25 am
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
September 22, 2012 at 9:41 am
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply