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?