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?
Sql-server – Backup chain for DBs restored from a different server
backuprestoresql server
Related Question
- Sql-server – Restoring differential backup from SQL Server 2005 to SQL Server 2012
- Sql-server – SQL Server restore differential after restoring full backup
- Sql-server – SQL Server Database Migration – Backup/Restore while Original DB still in Production
- Sql-server – Unable to create restore plan due to break in LSN chain
- Sql-server – SQL Server High Availability restore copy_only backups from secondary replica problem
- Sql-server – Do Log Backups depend on Differential Backups
Best Answer
Before running Ola's script, you can check the
msdb.dbo.backupset.type = 'D'usingtop(1) .. ORDER BY backup_finish_date desc.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.