May 23, 2011 at 6:41 am
I have a number of databases that change very seldom, and I would like to only back them up if they have changed since last time I backed them up. This allows me to both save time on the backup itself and bandwidth on the tasks that then replicate the backup elsewhere.
What's the best strategy?
Thanks in anticipation.
Tim
.
May 23, 2011 at 7:12 pm
Differential backups? They only back up pages that have changed since the last full backup, and are cumulative. This means that you only need to restore the last differential backup to get the recovery database to the same state as the primary database, unlike transaction log backups which need to be applied in the same sequence they were taken. However, transaction log backups allow you to restore to a specific point in time (assuming no bulk-logged operations), but not differential backups.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
May 23, 2011 at 8:41 pm
You could use this and read the DCM. That might be the most reliable
http://www.sqlservercentral.com/articles/Administration/differentialbackupprediction/2306/
June 13, 2011 at 6:46 am
Steve and Ray,
Sorry for the delayed response (I've been on holiday).
Thanks for the feedback. I was hoping there might be an easy way to check, but I guess I can acheive my objective by taking a full backup, then a daily differential backup until it looks too big (however I define that) and then start again with a full backup.
The reason is that I ship these backups up to the cloud automatically, and I want to minimise what is shipped!
Thanks again
Tim
.
June 13, 2011 at 6:57 am
I've had to do a similar-ish task lately. We needed to find which rows in all tables had been modified between 2 backups .
Needless to say that figuring that out requires a heck of a lot of processing power and a 2nd live copy of the DB.
If you really have a lot of time on your hands you could so some sort of snapshot of the db. Copy a rowversion column (assumin gyou ahve 1 in all tables) and the PK for each rows.
Then loop through all dbs / tables / rows to find how much data has changed and take a decision on that.
That would work but it might be cheaper just to buy more bandwith or a bigger package with the cloud!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply