0

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

  • 1
    Personal opinion, if this is a one time thing, and there is any confusion, I would go with the full backup. It's just safer. – Kenneth Fisher Nov 6 '14 at 16:51
  • Thanks Kenneth for your reply.. I am actually looking for a way to conclusively decide whether to issue a full or differential backup in an automated process and am not completely sure yet whether this query can be used to decide. How do other people do that, where you might like myself have an environment where a lot of restores can happen from "foreign" databases. – Remo Nov 7 '14 at 7:37
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

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.