Backup Databases on Creation

One of the companies which I work with has the occasion to create new databases when they do releases of their software.  Normally this isn’t a problem, except that they are setup to use maintenance plans to handle the backup and pruning of their transaction logs.  As all the new databases are created in the full recovery model this can end up causing some problems for them as within 12 minutes they start getting emails saying that the transaction log backup job has failed.  And these emails will keep coming in, possibly for hours until the full backup job kicks in later that night.

To solve this problem, I added a DDL trigger to the server which will cause the new database to be backed up as soon as the database is created.  The trigger itself is rather simple.  Most of the trigger is setting variables.  Then I make sure that the database isn’t a database snapshot, as database snapshots can’t be backed up.  If it isn’t a snapshot we continue with everything else.

Then I create a subfolder for the backups to be put into (the backups for each database go into their own folder, so as this is a new database the folder needs to be created).  Then I commit the transaction, as database backups can’t be taken within a transaction.  Then we do the actual database backup.  I then throw a message to the end user using the RAISERROR statement telling them that they can ignore the other error that they are going to get about the transaction being closed before the trigger was complete.  This is just an annoyance of my needing to commit the transaction before taking the backup.  Sure I could have setup a job which takes the backup and emails if there was a failure, but that just seems to complex for something so simple.  The code for my trigger is below.

CREATE TRIGGER BackupNewDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @database sysname, @event_data XML = EVENTDATA(), @folder nvarchar(4000), @file nvarchar(4000)

SET @database = @event_data.value(‘(/EVENT_INSTANCE/DatabaseName)[1]’, ‘sysname’)

set @folder = ‘X:Backups’ + @database

set @file = @folder + ” + @database + ‘.bak’

if exists (select * from sys.databases where name = @database and source_database_id is null)
BEGIN
EXEC master.dbo.xp_create_subdir @folder

COMMIT

BACKUP DATABASE @database to disk=@file

raiserror( ‘You can ignore the error message which says that the transaction ended within the trigger.’, 16,1)
END
GO

Hopefully you find this solution helpful if you get into a situation like this,
Denny