3

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?

3
  • I suggest you read this. – SqlWorldWide Apr 2 at 10:50
  • Why can't you use the solution of the thread you mentioned with a slightly different code on the trigger to just backup that specific database instead of running the job? – Ronaldo Apr 2 at 11:21
  • @Ronaldo: But at the linked thread the trigger just starts a backup job. This will start a full backup for all databases. To use the linked solution I would need do create a job to backup only the new database and inject the name of the newly created DB to the job? I don't know how to do this. – Phoniex Apr 2 at 12:23
3

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.

1
  • Thank you for the suggestion and the snippet. I was able to solve it with this. – Phoniex Apr 2 at 15:27
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.