0

When a DB is restored from a different server (say, Test is refreshed with Prod), the info on the differential base is brought with the backup. So, the SQL instance will keep relying on the backup chain that exists in the original environment (Prod) until a new full backup is taken (in Test). Ola’s backup solution is not able to detect and resolve this condition, so it would keep taking diff backups (even with @ChangeBackupType = 'Y') until the next scheduled full backup. Is there any workaround on that?

1
  • 2
    You could add a step after the restore job to take a full backup immediately after? In most cases when you perform this type of action, i.e. refreshing test server, you don't care too much about backups since it's implied the data here isn't the golden source. Mar 2 '16 at 18:25
1

Before running Ola's script, you can check the msdb.dbo.backupset.type = 'D' using top(1) .. ORDER BY backup_finish_date desc.

  • If it returns value then you have a full backup done and you can proceed with differential.
  • If it returns null or no value then you dont have a full backup and you can take a full backup and then proceed with your regular differential backups.

I would agree with JohnM that do you really need differential backups on a test server ? Its easy for you to get a full backup and refresh the environment.

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.