December 4, 2011 at 9:13 am
Yes I had copied them all but it would let me restore the latest one as it was created after a new full backup, so I only restores the diffs that occurred up to that point. Then I left only those TRN files that were created after that point in time.
I'll give it a day to make 100% certain I don't need the moved files, then will blow them away 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 4, 2011 at 9:34 am
MyDoggieJessie (12/4/2011)
Yes I had copied them all but it would let me restore the latest one as it was created after a new full backup, so I only restores the diffs that occurred up to that point.
You ONLY needed to copy and restore the last differential that ran before the next full backup, not all of them. Differentials are cumulative.
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
December 4, 2011 at 9:55 am
Thanks Gail, I will remember this going forward
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 6, 2011 at 9:40 am
You ONLY needed to copy and restore the last differential that ran before the next full backup, not all of them. Differentials are cumulative.
Gail, I have to ask...
Say I have a Full Backup - 88GB
Then a DIFF runs @6am - and it's 25GB
Another Diff runs @12pm - it's 4GB
Another Diff runs @6pm - it's 5GB
Another Diff Runs @12am - it's 9 GB
Why would my first DIFF backup be so large, and how on earth could loading the 9GB DIFF file include everything that was in the prior DIFFs?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 6, 2011 at 12:15 pm
MyDoggieJessie (12/6/2011)
You ONLY needed to copy and restore the last differential that ran before the next full backup, not all of them. Differentials are cumulative.
Gail, I have to ask...
Say I have a Full Backup - 88GB
Then a DIFF runs @6am - and it's 25GB
Another Diff runs @12pm - it's 4GB
Another Diff runs @6pm - it's 5GB
Another Diff Runs @12am - it's 9 GB
Why would my first DIFF backup be so large, and how on earth could loading the 9GB DIFF file include everything that was in the prior DIFFs?
It can't, there must have been a full backup of some form between 6am and 12pm. (any full backup, including one run by other backup tools)
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
December 6, 2011 at 2:26 pm
I have verified that the files were differential backups. I understand it seems very odd, however, these were all generated from a standard maintenance plan...bizzarre!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 6, 2011 at 2:38 pm
MyDoggieJessie (12/6/2011)
I have verified that the files were differential backups.
I never suggested they weren't
however, these were all generated from a standard maintenance plan...bizzarre!
Never suggested they weren't.
What I said was that a full backup probably ran between the 6am and 12pm differential backups, and that said full backup could be something other than a standard native backup.
Did you check the backup history tables?
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
December 6, 2011 at 2:48 pm
MyDoggieJessie (12/2/2011)
About 300 miles
😀
you want to try 6000miles 😎
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 7, 2011 at 2:14 am
MyDoggieJessie (12/6/2011)
You ONLY needed to copy and restore the last differential that ran before the next full backup, not all of them. Differentials are cumulative.
Gail, I have to ask...
Say I have a Full Backup - 88GB
Then a DIFF runs @6am - and it's 25GB
Another Diff runs @12pm - it's 4GB
Another Diff runs @6pm - it's 5GB
Another Diff Runs @12am - it's 9 GB
Why would my first DIFF backup be so large, and how on earth could loading the 9GB DIFF file include everything that was in the prior DIFFs?
It's not the general observation...
Per BOL:
The size of a differential backup depends on the amount of data that has changed since the base. Generally, the older a base is, the larger a new differential backup will be. A specific differential backup captures the state of changed extents at that time that backup is created. If you create a series of differential backups, a frequently updated extent is likely to contain different data in each differential. As the differential backups increase in size, restoring a differential backup can significantly increase the time that is required to restore a database. Therefore, we recommend that you take a new full backup at set intervals to establish a new differential base for the data. For example, you might take a weekly full backup of the whole database (that is, a full database backup) followed by a regular series of differential database backups during the week.
A differential backup that is taken fairly soon after its base is usually significantly smaller than the differential base. This saves storage space and backup time. However, as a database changes over time, the difference between the database and a specific differential base increases. The longer the time between a differential backup and its base, the larger the differential backup is likely to be. This means that the differential backups can eventually approach the differential base in size. A large differential backup loses the advantages of a faster and smaller backup.
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply