5

When new databases are created the Differential and transaction log backup maintenance plans start failing and sending alerts as full backup is not taken. What could be the best solution to handle it.

I tried to do full backup on database created trigger but that is failing as backup cannot be taken in the same transaction.

7

To achieve this task kindly follow below steps:-

  1. create server level trigger on create_database.
  2. Create sql job and add code to dynamic get the name of database and initiate backup.
  3. Add Code in the trigger to invoke job to create database backup.

e.g

CREATE TRIGGER ddl_trig_database   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS   
   EXEC msdb.dbo.sp_start_job N'backup_job' ;  
GO  
-1

I would generally recommend not to use the built-in maintenance plans in SQL Server.

I found Ola Hallengren's maintenance solution to be much more reliable: https://ola.hallengren.com/downloads.html

For example, it wouldn't try to perform log backups on a database that didn't perform a full backup yet.

  • 1
    I agree that Ola’s free maintenance scripts make the process a lot easier to manage, but the answer doesn’t really specify why or give an example in TSQL – clifton_h May 26 at 20:48
  • What do you mean the answer doesn't specify why? I wrote why in the last paragraph: because it won't try to do diff or log backups on new databases that didn't have full backup yet. And I gave a link for the download. – Eitan Blumin May 27 at 17:39

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.