4

Is there a way to either:

  • ensure that unmodified databases are skipped during SQL backups, or even better
  • choose between DIFF or FULL backup depending on the percent database changed since last full backup?

We are using MS SQL Server 2008 R2 + Ola Hallengren's maintenance scripts, ~500 databases - total data files 500 x 2GB, and only about 5% databases get modified each week - but the backup procedure transfers all data files on each full backup.

Background:

Our MS SQL 2008 R2 server is configured to make daily DIFF backups and weekly FULL backups, using the "widely known" Ola Hallengren's maintenance scripts. The system is configured for multi tenancy, and databases are also physically split not only by customers, but also by months. Since the system tracks these databases very well, this makes the system a bit easier to maintain; each customer*month datafile can be located wherever you choose, individual files are smaller in size and easier to manage, and most of the r/w queries for a single customer only go into the last month's db, making them more performant (smaller indexes, less locking, etc.). Recovery model is set to SIMPLE, but losing data is not such an issue for us since these measurements are collected from distributed devices (which also have their local storages) and can re-transmit data automatically.

But the problem is that the backup procedure is becoming bizarrely slow, because each backup copies even the old, unmodified (well, presumably unmodified) backups over and over again. Normally, only the databases for the last month are being modified at any time, so we would only like to backup these weekly. I checked that old databases don't get accidentally modified by index optimization or whatever: e.g. running fn_dblog on old databases only shows some entries seemingly added by the backup procedure, like "CommitDifferentialBase" or "InvalidateDiffMaps".

Update

Using the link from @spaghettidba's answer, I slightly modified Paul Randal's stored procedure to simply return the percentage (to make it slightly easier to get the return value):

-- use at the end of 'sp_SQLskillsDIFForFULL' 
return ROUND ((CONVERT (FLOAT, @DIFFtotal) / CONVERT (FLOAT, @sizeTotal)) * 100, 2);

And then I added this check inside Ola Hallengren's DatabaseBackup stored proc:

-- get the percent of changed extents since last full backup
EXEC @CurrentDbChangedPercent = sp_SQLskillsDIFForFULL @CurrentDatabaseName
IF @AutoSelectDiffOrFullOnChange = 1 AND @DiffFullChangePercentThreshold > 0
BEGIN
  IF @CurrentDbChangedPercent <= @DiffFullChangePercentThreshold 
    BEGIN  
      SET @CurrentBackupType = 'DIFF'
    END
  ELSE
    BEGIN  
      SET @CurrentBackupType = 'FULL'
    END
END

As Mr. Hallengren pointed out, DBCC PAGE command used in Paul's proc is not really supported/documented, and this is the main reason this isn't currently a feature of his SQL Server Maintenance Solution. Few people except Paul would be able to parse this data properly.

There is a request to get a supported DMV for this on Microsoft Connect, dating from 2009:

  • I wrote myself a script that parses all the diff maps and tells me how many pages were modified. I'm using that for certain purposes. You could incorporate this data into the backup policy somehow. – usr Jun 12 '15 at 10:58
  • But maybe it is enough to move all FULL backups to monthly and have daily diffs. Note, that you can delete full backups and their corresponding diff backups after taking them without breaking the log chain. That way you can trim storage space after the fact. – usr Jun 12 '15 at 10:59
  • @usr: Thanks, this is probably similar to the stored proc by Paul Randal linked in the answer below? I used that to force DIFF backups if there are only a few modified pages and it works much better now. – Groo Jun 12 '15 at 12:30
  • Yes, I did not see that link. Seems to be the same thing. – usr Jun 12 '15 at 12:32
4

Skipping the backup altogether is not a good idea in my opinion. You should probably keep on taking differential backups instead. They will probably be very small and not a concern at all.

Detecting the percentage of data that changed since last FULL backup can be done using this function by Paul Randal.

  • Thanks, I am looking into Paul Randal's sp right now - although I am having trouble returning the value from the sp (I expected INSERT-EXEC would work, but his sp already uses that and these calls cannot be nested, crap). – Groo Jun 12 '15 at 11:27
  • Too bad. You could modify the code to write to a #temp table. – spaghettidba Jun 12 '15 at 12:08
  • At the end I modified Paul's script slightly to simply return a single value containing the percentage of modified extents, and then used it directly in the exec command. Thanks! – Groo Jun 12 '15 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.