SQL Server – Automatic Full Backup for New Databases Created with EntityFramework-Migrations

entity-frameworkmulti-tenantsql server

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?

Best Answer

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.

Related Question