Full backup on database creation

Question

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.

• KASQLDBA: itknowledgeexchange.techtarget.com/sql-server/… should help

Answer 1

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 

Answer 2

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.

• clifton_h: 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
• Eitan Blumin: 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.