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

All we need is an easy explanation of the problem, so here it is.

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 [email protected] and backup_set_id=(select max(backup_set_id) from msdb..backupset where [email protected] )
    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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply