Full backup on failure

Question

I have created a maintenance plan to take a full backup of all my databases every Sundays and a differential backup every day. Also, a log backup is triggered every hour.

However, there is an issue with this schema. If I add a new database in the middle of the week, the differential and log backup jobs will fail because they are lacking a full backup image.

I therefore need to be able to take a full backup anytime either the differential or log backup job fails.

I could for example add a new step to take a full backup in case the previous one fails. However, this full backup job will run on all my databases but I would like to only take the one that have failed.

Any way to ask the SQL server to take a full backup of new databases? It can right after the database creation or in case a of failure of a differential or log backup job.

Answer 1

Unfortunately the maintenance plans in SQL Server are quite limited in functionality and the option that you are looking for doesn't exist as far as I know.

As Shanky has mentioned, you would need to look at some other tool for this such as the excellent open source backup and maintenance solution from (Ola Hallengren).

This has the specific backup parameter that you are looking for @ChangeBackupType = 'Y'. From the documentation

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.

Answer 2

I think it'd be wise to know you don't have a FULL backup on a newly created database as soon as possible (rather than waiting for a job to fail), but I'm not sure I'd try to automate the FULL backup at creation time.

You might consider Chad Churchwell's approach in generating Email alerts when new databases are created in SQL Server

He suggests creating a SERVER TRIGGER which can be used to send an email notification to the DBA group anytime a new database is created.

Here is the example script from Chad's post:

USE master
GO

CREATE TRIGGER [ddl_trig_database]
ON ALL SERVER
FOR CREATE_DATABASE
AS
declare @results varchar(max)
declare @subjectText varchar(max)
declare @databaseName VARCHAR(255)
SET @subjectText = 'DATABASE Created on ' + @@SERVERNAME + ' by ' + SUSER_SNAME() 
SET @results = 
  (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @databaseName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))

--Uncomment the below line if you want to not be alerted on certain DB names
--IF @databaseName NOT LIKE '%Snapshot%'
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'SQLAlerts',
 @recipients = '[email protected]',
 @body = @results,
 @subject = @subjectText,
 @exclude_query_output = 1 --Suppress 'Mail Queued' message
GO

The following items will need customization in your environment.

• @profile_name = The database mail profile to use to send the email.
• @recipients = The distribution group email address to send the email to within your organization.

If you actually DID want to generate an automatic FULL backup at database creation time, you could dynamically generate the BACKUP command in the trigger.

Answer 3

There is a workaround for that issue.

It will require an additional table,a trigger, and an agent job to be ran before transaction log backup job. So if you think its worth going through that than bare with me:

You should create a table where you should add Database name,whether database was updated or not (you will need this for an agent), and some other info needed for backups such as :

use master GO create table dbo.Backups (DBName nvarchar(50),CreatedDate date,BackUPName as DBName + '_' + cast(CreatedDate as varchar(25)), BackedUp bit)

Create a trigger that inserts fills the table on newly created DBs

ALTER TRIGGER BackupDBAfterCreation 
ON ALL SERVER
AFTER CREATE_DATABASE
AS 
BEGIN

DECLARE @DatabaseName varchar(50)
SET @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(50)')

Insert into dbo.Backups values (@DatabaseName,GETDATE(),0)

END

Next step is to create a job that will check (before tran log backup job):

Step 1. Whether there is a database with BackedUp value of 0 (meaning not backed up)

Step 2. Back it up taking values from the table (DB name,BackupName)

Step 3. Update column BackedUp to 1.

Optional: Send an email informing DBAs about newly backed up database.