SQL Server – Automatically full backup for new databases which are auto-created with EntityFramework-Migrations

I have an ASP.NET MVC Application with a Multi-Tenant architecture (using SQL-Server). I’m using EntityFramework DB-Migrations which automatically generates a database per customer.

In SQL-Server I have set up 3 maintenance plans: full, diff and transaction backups. The full-backup happens only once a day.

Now if a new customer-database is auto-generated, the next transaction/differential-backups will fail because there isn’t any full-backup available.

I have found following thread with a similar problem: full-backup-on-database-creation

But in this case a full-backup is created for all databases – which could lead to a longer run. I just want to do a full-backup for the new database (which is empty and should be finished within seconds).

I have tried this by extracting the code from the maintenance plan:

ON ALL SERVER 
FOR CREATE_DATABASE
AS 

SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @db sysname = @EventData.value(N'(/EVENT_INSTANCE/DatabaseName)[1]', N'sysname');
    DECLARE @SubDir nvarchar(1024) =  N'PATH TO BACKUP-LOCATION' + @db;
    DECLARE @BackupName nvarchar(1024) = @db + N'_backup_initial';
    DECLARE @BakFile nvarchar(1024) = @SubDir + N'' + @BackupName + N'.bak';
    DECLARE @ErrMessage nvarchar(1024) = N'Verify failed. Backup information for database ' + @db + ' not found';
    declare @backupSetId as int

    EXECUTE master.dbo.xp_create_subdir @SubDir
    
    exec('BACKUP DATABASE ' + @db + ' TO  DISK = ''' + @BakFile + ''' WITH NOFORMAT, NOINIT,  NAME = ''' + @BackupName + ''', SKIP, REWIND, NOUNLOAD, COMPRESSION, ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [BackupCert]),  STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR')
        
    select @backupSetId = position from msdb..backupset where database_name=@db and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@db )
    if @backupSetId is null begin raiserror(@ErrMessage, 16, 1) end
    --RESTORE VERIFYONLY FROM  DISK = @BakFile WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
    
GO

Executing the backup-script manually is working, but within the trigger it fails with an error message:

cannot perform a backup or restore operation within a transaction

Does anyone know how to get this working?

Does anyone know how to get this working?

Don’t run it directly from the trigger. Instead write an Agent job to backup any FULL recovery database that hasn’t had a full backup. This state is sometimes called “Pseudo-Simple Recovery“, as it behaves just like SIMPLE recovery even though the database recovery model has been changed to FULL.

Then either run that every 5 min, kick off the job with sp_start_job from the trigger, or integrate it into your scheduled maintenance so it runs before your log backups.

EG run a cursor over

select d.name
from sys.databases d
join sys.database_recovery_status ds
  on d.database_id = ds.database_id
where recovery_model_desc = 'FULL'
and last_log_backup_lsn is null

and take full backups.

my suggestion is to replace your script with one that is using ola hallengren backup solution with the option ChangeBackupType=’Y’

https://ola.hallengren.com/sql-server-backup.html

ChangeBackupType
Blockquote

Change the backup type if a differential or
transaction-log backup cannot be performed.

DatabaseBackup checks differential_base_lsn in
sys.master_files to determine whether a differential backup can be
performed. If a differential backup is not possible, then the database
is skipped by default. Alternatively, you can set ChangeBackupType to
Y to have a full backup performed instead.

DatabaseBackup checks last_log_backup_lsn in
sys.database_recovery_status to determine whether a transaction log
backup in full or bulk-logged recovery model can be performed. If a
transaction log backup is not possible, then the database is skipped
by default. Alternatively, you can set ChangeBackupType to Y to have a
differential or full backup performed instead.

Basically, if you have this set to Y, then when the scheduled tlog for all user_databases runs, then the script will take a full backup instead of just a tlog.