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.
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".
-- 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
IF @CurrentDbChangedPercent <= @DiffFullChangePercentThreshold
SET @CurrentBackupType = 'DIFF'
SET @CurrentBackupType = 'FULL'
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: