| DHatheway 2005-05-31, 11:23 am |
| I was thinking about seting up an automatic backup process that would
automatically decide whether to do a full backup or a differential backup,
with determination being made based on the anticipated size of the
differential backup and the number of days since the last full backup. If
we had a full less than a week old and were expecting a differential less
than, say, 25-30% of the full backup size, we'd do a differential.
Originally, I was thinking the estimate of differential backup size would be
based on the size of the last differential but it semed to me that there
must be a better way to figure this out (the last differential size is
usually a decent predictor but a sudden massive table change would make it
entirely wrong).
Thanks to Kalen Delany's excellent book, "Inside Microsoft SQL Server 2000",
I found that I can use DBCC PAGE to look at individual pages and I can
locate the change map page and read it but I can only seem to get this
output as a hex dump formatted message. Reading that and parsing it looks
like something of a nuisance.
Anybody know a better way to read the change map page directly? Reading
only the actual bytes on the page into some sort of byte array or varbinary
or into a table? Any thoughts?
Am I overlooking another way to access individual database pages?
Ideally, I'm aiming for a pure T-SQL / SQL Server solution. Using DBCC it
looks as though I might have to write a VB or .NET program to make the
process effective.
Finally, why am I doing this? I'm interested in minimizing the amount of
data that gets sent up to the network backup server every evening. Instead
of writing a 40GB full backup to the backup server every day or 280GB/week,
I'm thinking I can backup (40+(7*diffsize))GB/week.
|