Do you have any idea where the server keeps information on the backup

Question

Do you have any idea where the server keeps information on the backup

Sameh Mohamed

SSC Veteran

Points: 224
More actions
March 16, 2005 at 7:12 am

#163975

Hi
Do you have any idea where the server keeps information on the differntional backup.
I need to know which rows where changed since the last backup. I assume there is a place where the server keeps information on records changed since last backup in case the user needs to make differentional backup.
Do you have any idea about that??
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jason Hall Hall of Fame Points: 3906 More actions · Answer 1

The way that differential backups are handled is as follows. SQL Server databases are broken up into pages, this way pages can be moved into memory for quick accessing. When any bit of data on a page is changed, the page is marked as "dirty". When you do a differential backup, the backup process grabs all of the dirty pages. If no bits of info have changed on the page, it does not get backed up. When a full backup takes place, all of the dirty pages get marked as clean and the process starts over.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

Sounds like you might need a log reader tool like Log Explorer or PI Log.

Greg

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Jason's got it right. SQL Server doesn't concern itself with rows when backing up. Too inefficient. You can see that the structure is defined if you look at Books Online. Index Tab >> pages, described.

If you need to know what changed, Greg's right. A log reader tool is needed. You can also set up auditing via triggers, but that's a lot of probably unnecessary work. Another tool other than the one Greg mentioned is ApexSQL Log.

K. Brian Kelley
@kbriankelley

Sameh Mohamed SSC Veteran Points: 224 More actions · Answer 4

Thanks every one ...

Is there any way to read and use this log using t-sql . For instance like exporting the changes into an external file?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

You might want to try dbcc log( your_db, -1) first. However, I doubt, you will be very happy with what you see there.

But what are you really after anyway?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Sameh Mohamed SSC Veteran Points: 224 More actions · Answer 6

I am trying to write the changes made to a database into a file and update another remote database with such changes.

Kelly suggested to set up auditing via triggers which is a bit tiring so I thought of this backup thing which I have not tried it yet.

The problem is that I am having clients who use the same database at different locations. And implying replication for this would be almost impossible.

So I am trying to find an easier way for moving changes from one instance to the other!

Any suggestions???

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

One might be inclined to think, you're trying to reinvent the wheel.

Isn't that something for log shipping?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Greg Charles SSC-Forever Points: 45403 More actions · Answer 8

I agree with Frank. This has been done before. Consider log shipping or replication for close-to-real-time data movement or DTS for more infrequent movement.

Greg

Sameh Mohamed SSC Veteran Points: 224 More actions · Answer 9

Well, You are talking about stand by servers indeed. where servers provided with the backups or logs are read only servers.

My problem actually requires merge replication, where two machines are processing new data, and we have to move the data from one machine to the another through an off-line process.

But replication is too complicated and requires an attending DBA. But I am dealing with small businesses who cannot afford a permenant connection between two locations.

They require to transfer data changes made at branches and have them fed to the hq in a one way direction. So the hq database shall have the data for the whole company while the branches will only keep their own data.

I tried merge replication once and it was pain in the neck, not to mention the burden of programming.

So I need a more simplified replication, a custom one. I thought of triggers which will mark data changed in every table and simply copy them to an exteranl file later. Then I will have a small custom conflict solving.

I had a thought about finding the changed data through the servers logs as mentioned in my main post!!

Any suggestions??