May 31, 2016 at 2:32 am
In our production SQL Server 2012 Environment, we use the well-known backup utilities from Ola Hallengren. It is configured to produce a full back-up very early on Sunday morning, and a differential back-up at the same time on each other day of the week. Log backups are taken every 15 minutes.
On one particular server, the main database regularly produces a full back-up of about 8GB. Each differential back-up is normally in the range between 100 and 500MB.
Last week, I took a Copy Only full backup of this database, and used it to overwrite one of our test and development databases. Afterwards, each differential backup was 5GB in size. This continued until the Sunday full back-up happened, after which the differential back-ups returned to their normal size range.
Please can someone explain to me why a Copy Only backup should increase the size of the differential backup files? I thought that the main idea of a "Copy only" backup was that it was excluded from the chain of backups, or am I wrong about this?
Thanks in advance to all who respond.
May 31, 2016 at 3:25 am
A copy-only backup won't affect the size of differentials.
What else happened around the same time? Index rebuilds?
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
May 31, 2016 at 3:35 am
I really don't know...
The only unusual happening that I could track down on that day was the Copy Only backup: if it's not that, then I am baffled.
I will keep monitoring it for the next few weeks and see if it repeats itself.
Thanks for getting back to me.
May 31, 2016 at 3:47 am
The way diff backups work:
Any change made to a page results in a bit being set to 1 in the differential change map for the extent that contains the page. (1 differential change map per 4GB, 1 bit per extent)
A diff backup contains all extents that had their bit set to 1.
A normal full backup resets all differential change maps to 0
A copy-only full doesn't touch the differential change maps
Index rebuilds, since they recreate the index, result in lots of changed extents. Reorganise does too. Otherwise look for anything that's changed a lot of data, even in small ways.
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
May 31, 2016 at 3:51 am
Many thanks for this advice.
May 31, 2016 at 1:52 pm
GilaMonster (5/31/2016)
A copy-only backup won't affect the size of differentials.What else happened around the same time? Index rebuilds?
or emptying and loading of large tables maybe
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply