backup

Question

backup

ramyours2003

SSChampion

Points: 12268
More actions
March 2, 2016 at 7:00 am

#316483

How sql server knows when there are any chages in database while taking the differnetial backup and log backup ?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I copied and pasted those very words into my favourite search engine. The question was answered in the very first result.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Well a transaction log is just a backup of the transaction log, and so there's no need to know what's changed to do that.

For differential backups, google: differential change map

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

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

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 3

As others have said, the log backup is the easiest one to explain. Any completed transactions that are currently in the log are what get backed up. Those completed transactions that have been backed up then get removed from the log. The next time you run a backup of the log, it only backs up any new transactions that have been completed (removing those). There's really nothing for it to track there. It doesn't know what it backed up previously. It just knows that what's in the log has to be backed up.

Gail gave you the other answer.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Grant Fritchey (3/2/2016)
Any ~~completed~~ transactions that are currently in the log are what get backed up.

Uncommitted also get backed up.

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

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

8adger Right there with Babe Points: 768 More actions · Answer 5

See Paul Randals post. Very good

http://www.sqlskills.com/blogs/paul/new-script-how-much-of-the-database-has-changed-since-the-last-full-backup/

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 6

GilaMonster (3/2/2016)
Grant Fritchey (3/2/2016)
Any ~~completed~~ transactions that are currently in the log are what get backed up.
Uncommitted also get backed up.

I always forget that part of how it works. I need a new brain.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning