patternsqlMinor
Preventing full backups for unmodified/slightly modified databases
Viewed 0 times
fullbackupsdatabasesmodifiedforpreventingunmodifiedslightly
Problem
Is there a way to either:
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
Update
Using the link from @spaghettidba's answer, I slightly modified Paul Randal
- 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
Solution
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.
Detecting the percentage of data that changed since last FULL backup can be done using this function by Paul Randal.
Context
StackExchange Database Administrators Q#103873, answer score: 4
Revisions (0)
No revisions yet.