June 1, 2011 at 12:46 pm
What is the difference between incremental backup and transaction log
Thank you
June 1, 2011 at 12:57 pm
Incremental backup is your database backup and trasaction log backup is your log backup. Increamental backup can be done on your database irrelavant of database recovery model and take backup of the change pages since last full backup.
Transaction log backup only be done when your database is FULL or BULK-LOGGED recovery mode and not allowed when database in SIMPLE recovery.
Trasaction log backup will allow you to recover to point in time of the failure while incremental backup won't.
June 1, 2011 at 1:01 pm
There's no such thing as an incremental backup in SQL Server.
There are full backups, differential backups and transaction log backups.
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
June 1, 2011 at 1:14 pm
Thank you
June 3, 2011 at 4:58 pm
Transaction log backup can be seen as equivalent of incremental backup, because to restore you need last full backup and all the increments (transaction log backups) until the point-in-time desired to restore.
With differential backup, you need last full backup and just the last differential backup to restore to the point when diff backup was taken. After that you can restore tran. log backups until desired point-in-time you want to restore.
So, there really are kind of full, differential, and incremental backup concepts in sql server.
Backup software, like backup4all (google that, or use direct link: http://www.backup4all.com/en/help/backup_types.html), have really well defined backup concepts and distinctions between them.
June 3, 2011 at 5:05 pm
Vedran Kesegic (6/3/2011)
So, there really are kind of full, differential, and incremental backup concepts in sql server.Backup software, like backup4all (google that, or use direct link: http://www.backup4all.com/en/help/backup_types.html), have really well defined backup concepts and distinctions between them.
No, actually, there isn't.
Using the catchall wikipedia: http://en.wikipedia.org/wiki/Incremental_backup
An incremental backup preserves data by creating multiple copies that are based on the differences in those data: a successive copy of the data contains only that portion which has changed since the preceding copy has been created.
A transaction log backup is much more detailed then a serialized static point in time series. Incremental Backups are what differentials would be if you had to restore them all since the last backup. A transaction log backup can restore to a point *between* backups of the file, allowing for more granularity.
A transaction log backup can allow for incremental backup strategies, but it is much more comprehensive.
And from your link:
An incremental backup stores all files that have changed since the last full, differential or incremental backup. The advantage of an incremental backup is that it takes the least time to complete. However, during a restore operation, each incremental backup must be processed, which could result in a lengthy restore job.
Which is similar. However, it is not possible in either definition to restore to a point in time between backups, which transaction log backups allow.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 3, 2011 at 5:35 pm
Good point, Craig! Transaction log backup really is more powerful than plain incremental backup!
September 19, 2011 at 3:46 am
Amu (6/1/2011)
Trasaction log backup will allow you to recover to point in time of the failure while incremental backup won't.
In BULK_LOGGED recovery model this is not necessarily true and shouldnt be relied upon. If the log backup encounters bulk logged changes PIT restores will not be possible.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply