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?
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.