I have created a maintenance plan to take a full backup of all my databases every Sundays and a differential backup every day. Also, a log backup is triggered every hour.
However, there is an issue with this schema. If I add a new database in the middle of the week, the differential and log backup jobs will fail because they are lacking a full backup image.
I therefore need to be able to take a full backup anytime either the differential or log backup job fails.
I could for example add a new step to take a full backup in case the previous one fails. However, this full backup job will run on all my databases but I would like to only take the one that have failed.
Any way to ask the SQL server to take a full backup of new databases? It can right after the database creation or in case a of failure of a differential or log backup job.
Best Answer
Unfortunately the maintenance plans in SQL Server are quite limited in functionality and the option that you are looking for doesn't exist as far as I know.
As Shanky has mentioned, you would need to look at some other tool for this such as the excellent open source backup and maintenance solution from (Ola Hallengren).
This has the specific backup parameter that you are looking for @ChangeBackupType = 'Y'. From the documentation