Preventing full backups for unmodified/slightly modified databases 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 &gt; 0
BEGIN
  IF @CurrentDbChangedPercent &lt;= @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:


DMV to check the percentage of a database changed since the last full backup
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.