SQL Server with Mr. Denny

Nov 17 2012   2:00PM GMT

Backup Databases on Creation

Denny Cherry Denny Cherry Profile: Denny Cherry

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: