SQL Server : how to decide if FULL backup or DIFFERENTIAL

Question

The following scenario (on SQL Server 2008 R2 and 2012 servers)

A co-worker of mine restored another DB into the one our process was trying to create a differential backup for. As I am currently only checking for the existence of any row for that respective DB in the msdb.dbo.backupset, a differential backup has been initiated - a row has been found. It failed, because the DB was not the one matching the full backup.

Now, to decide whether a full backup needs to be taken or a differential backup can be initiated, I would like to know exactly if the db uid and the backup's uid match - only then I want to create a differential backup, otherwise first create a full backup.

Can anybody tell me if the following is a sensible thing to do:

SELECT 
    sysdb.name 
FROM 
    sys.databases sysdb 
INNER JOIN
    sys.master_files sysmf ON sysmf.database_id = sysdb.database_id
INNER JOIN
    msdb.dbo.backupset bck ON bck.backup_set_uuid = sysmf.differential_base_guid
WHERE 
    sysdb.name = @dbname 
    AND bck.database_name = sysdb.name 
    AND bck.type = 'D'

Thank you very much in advance for a feedback,

Cheers

Answer 1

to decide whether a full backup needs to be taken or a differential backup can be initiated, I would like to know exactly if the db uid and the backup's uid match - only then I want to create a differential backup, otherwise first create a full backup.

The closest you can get is to use sp_SQLskillsDIFForFULL - from SQLSkills.com which will tell how much of the database has changed since the last full backup.

You can decide a threshold based on your company's backup policy and then have a job that executes a FULL or differential backup based on the threshold.

Remember that a FULL backup (without COPY_ONLY) will reset the differential base, so be cautious for any adhoc FULL backups taken and make sure that you test your restore policy to ensure that data loss is acceptable !

From Paul's blog

The basic idea behind the code is as follows:

For each online data file in the database
   For each GAM interval in the file
      Crack the DIFF map page using DBCC PAGE
      Interpret the DIFF bitmap to aggregate the changed extents
      Add the sum to the total changed extents for the database
   End
End
Report results