6

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.

shilan
  • 119
  • 3

2 Answers2

9

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
Vinod Narwal
  • 407
  • 2
  • 5
-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.

Eitan Blumin
  • 483
  • 4
  • 11