Schedule Full, Differential and Transactional Backup in SQL Server 2016

Question

I studied about scheduling backups with SQL Server Agent and Maintenance Plans (which leads to the SQL Server Agent).

My first concern is, what is the best way to schedule my backups?

I want full backup every day, differential backup every hour and transactional log backup every 15 mins.

My second concern is, is this a good practice?

I noticed an issue when I used SQL Server Agent. My full and differential backups are overwritten. I am ok with having the full backup overwritten but not for the differential since when I need to recover it, I would only have one differential backup which is not the purpose of differential backups.

How can I not let this overwrite on previous backups?

The last concern and question is regarding the way to implement the backup scheduling. I am going to use Maintenance Plans , and may schedule three different backups. One for full, one for differential and one for transnational.

Is this the best practice for a database that is used everyday by users? (at least 2000 transactions per day)

• Ken White: I presume you mean transactional instead of transnational? Transactional means that it logs all transactions, while transnational means between more than one country.
• Squirrel: append date and time string to you backup filename. This way it will not overwrite each other

Answer 1

Adding to the answer of Shekar Kola.

Personally I always suggest and use custom scripts over maintenance plans as these are easier to deploy on multiple servers, and easier to configure.

If you're going for the route of the custom scripts instead of the maintenance plans, consider using Ola Hallengren scripts. Ola hallengren website

These are some amazing well documented and frequently used scripts that cover everything about backup, index maintenance and itegrity checks. These scripts get recommended by some of the best DBA's around.

The backups wont overwrite eachother and the script will add dates to the .bak files.

The only thing you have to do is run the scripts, and add some schedules yourself. IF you want more configuration, check the documentation, there's a lot you can add/change yourself if you'd like.

UPDATE:

Is it fine to have system database backup everyday?

Yes nothing wrong with it.

and Differential users database backup every 4 hours and Log backup every 15 mins.

Doing a diff during work hours can put a lot of stress on your database, and can hurt performance A LOT. Often this is not acceptable. Again, depends on your case, how long backups take, how often your data changes.

Do you think this is good enough for a a database that has 2000 transaction per day (approximately)?

The amount of backups are normally decided by the business. You should look up RTO and RPO SQLSkills RTO/RPO. How many backups and which backups you need is related to those numbers, and to the amount of disk space you can use. An often used schedule is, 1 full each week, 1 diff each day, log backups every 10 - 15 min. But it of course depends on your case.

I do have other jobs such as 'DatabaseIntegrityCheck - USER_DATABASES' , 'IndexOptimize - USER_DATABASES' ,etc. Should I schedule them too? or they will be used automatically with backups?

No they will not be fired with the backups, they are totally separate jobs. But yes, you should have some kind of Integritychecks and index maintenance running. Read up on the documentation of what exactly they do. Very short: Integrity checks check your data for corruption, and IndexOptimize will check your indexes for fragmentation.

But as a starter, just make sure you take backups, it's much more important to have backups not scheduled perfectly, than not having backups at all.

• lili: This is amazing. Thank you so much.
• lili: Dear Yannick, I wanted to schedule the backups now. I have several jobs that I can schedule it. I was thinking to have full user and system databases backup everyday (Is it fine to have system database backup everyday?) and Differential users database backup every 4 hours and Log backup every 15 mins. Do you think this is good enough for a a database that has 2000 transaction per day (approximately)? I do have other jobs such as 'DatabaseIntegrityCheck - USER_DATABASES' , 'IndexOptimize - USER_DATABASES' ,etc. Should I schedule them too? or they will be used automatically with backups?
• Yannick Liekens: @lili Updated my answer, hope it's helpful to you.
• lili: Amazing! Thank you so much

Answer 2

Considering following points may help you to plan backup schedule properly:

You can schedule your backups with following methods other than Maintenance plan

1. Custom script (stored procedure) - SQL Agent
2. Any third party tools

I personally would like to go with custom script as it's flexible.

Here are some basic notes about 3 type of backups (hope you might aware already)

Full backup

1. Contain full copy of the database including all data files (in case file groups in-place)

2. Consider the storage for full backup as it cannot be less than 15 to 20% of whole database size even with compression enabled

3. Recovery would be easy and faster compare to LOG
4. Not recommended to do more frequently as it would required more resources from server compare to other backup types, always perform while there is not much activity on database.

Differential backup

1. Contain only the changes that occurred after the last full backup

2. The recent Differential backup would contain all the previous Differential backup data which after the last full backup. i.e.
   0- FULL
   1- DIF1
   2- DIF2
   3- DIF3 - this file would have the data that been backed-up in DIF1 and DIF2, it's unlike LOG backups

3. Recovery cannot be possible without having FULL backup available

4. FULL BACKUP that been performed with COPY ONLY option cannot be starting point of the Differential backup'. as usual, FULL BACKUP is start-point for theDifferential backup' as well as 'LOG backup'

LOG backup

1. Contain only the changes that occurred after the last full backup

2. Consider only when DATABASE RECOVERY set to FULL, otherwise no need (not possible)

3. Unlike differential backup, it should have all LOG BACKUP files to be available in sequential basis otherwise cannot be restored. i.e. 0- FULL
   1- LOG1
   2- LOG2
   3- LOG3

Although, LOG backup meant for point in time recovery, in case if LOG 2 not available for some-reason recovery not possible till the time when LOG3 backup happened. 4. Recovery time would be more as it have to re-enter all transactions from log file wherein 'FULL' and 'Differential' like a file copy

Following is example custom script that you can start with, or you may use Ola Hallengren backup script which trusted by many DBAs and free of cost:

The backup script creates stored procedure in your desired database, once created you can do following configuration:

1. Create JOB each backup type i.e. (JOB1-Full, JOB2-Differential, JOB3- Log) with desired schedule
2. Every database goes with different step in the JOB
3. It's up to your requirement: Either, let job proceed when a database backup failure within steps flow or fail the job and reports via e-mail notification

Backup Script

CREATE PROCEDURE [dbo].[DoBackup]
 @DatabaseName nvarchar (50) = Null,
 @BackupType nvarchar (25) = Null
AS
BEGIN
 SET NOCOUNT ON;
 Declare @ShareDrive nvarchar (max),
 @ServerName nvarchar (50),
 @BackupPath nvarchar (250),
 @BackupFilePath nvarchar (500),
 @VersionNumber nvarchar (50),
 @BackupDateTime nvarchar (20),
 @FileExtension nvarchar (5),
 @xpcommand varchar (1000),
 @IsPrefReplica bit;
 Declare @is_DB_HADREnabled bit;
 Declare @is_Server_HADREnabled bit;
 Declare @isPrimaryReplica bit;
 if @DatabaseName is null 
 begin
 set @DatabaseName = DB_NAME();
 end
 if @BackupType is null 
 begin
 set @BackupType = 'Full';
 end
 begin
 set @ShareDrive = '\\SharedServer\Backup$\DatabaseBackups\Production\';
 end
 set @ServerName = (select cast (SERVERPROPERTY ('MachineName') as nvarchar (20)) );
 set @BackupPath = (select @ShareDrive + @DatabaseName + '\' ) ;
 set @VersionNumber = (select cast (SERVERPROPERTY ('productversion') as nvarchar (20)) + '_');
 set @FileExtension = '.bak';
 set @BackupDateTime = 
 ( select convert (nvarchar (20), getdate (), 112) + '_' +
 cast (datepart (HH,getdate () ) as nvarchar (2)) + 
 cast (datepart (MINUTE, getdate () ) as nvarchar (2))
 );
 -- Validate is the replica is preferred for backup.
 select @IsPrefReplica = sys.fn_hadr_backup_is_preferred_replica (@DatabaseName);
 select @is_DB_HADREnabled = IIF(group_database_id IS NULL, 0,1) from sys.databases where [name] = @DatabaseName;
 select @is_Server_HADREnabled = Cast(SERVERPROPERTY ('Ishadrenabled') as int);
 BEGIN
 IF EXISTS (select db.name
 from sys.dm_hadr_database_replica_states as hadr
 join sys.databases as db on hadr.group_database_id = db.group_database_id
 where is_local = 1 and is_primary_replica = 1 and db.name = @DatabaseName
 )
 SET @isPrimaryReplica = 1 
 ELSE 
 SET @isPrimaryReplica = 0 
 END
 BEGIN
 -- =============================================================================================================================
 -- backup script
 -- =============================================================================================================================
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 IF (@BackupType like 'Log%')
 BEGIN
 --IF exists (select * from msdb.dbo.backupset where database_name = @DatabaseName and cast (backup_finish_date as date) = cast (getdate () as date))
 IF exists (select 1 from sys.databases where [name] = @DatabaseName)
 begin
 -- When database not part of Availability Group @IsPrefReplica woulbe become "1", so backup can be execute Non AG databases
 IF @IsPrefReplica = 1
 BEGIN
 -- ---------------------------------------------------------------------------------------------------------------
 -- creating directories if not available with server name and DB name
 -- ---------------------------------------------------------------------------------------------------------------
 BEGIN
 exec sp_configure 'show advanced options', 1;
 reconfigure;
 exec sp_configure 'xp_cmdshell', 1;
 reconfigure;
 BEGIN
 Select @xpcommand = ('if not exist "' + @BackupPath + '" mkdir ' + ('"' + @BackupPath + '"') );
 Exec xp_cmdshell @xpcommand;
 END
 exec sp_configure 'show advanced options', 1;
 reconfigure;
 exec sp_configure 'xp_cmdshell', 0;
 reconfigure;
 exec sp_configure 'show advanced options', 0;
 reconfigure;
 END
 -- Actual Backup command Begins---------------------------------------------------------------------------------------------------------------
 set @BackupType = 'Log_';
 set @BackupFilePath = (@BackupPath + @DatabaseName + '_' + @ServerName + '_' +@BackupType + @VersionNumber + @BackupDateTime + @FileExtension);
 BACKUP Log @DatabaseName TO DISK = @BackupFilePath with compression;
 Print 'Backup Created as '+ @BackupFilePath
 END
 ELSE Print 'Its not preffered replica for the backup '
 end
 Else 
 -- Print 'Fullbackup has been already done today! ' + @BackupFilePath
 Print 'Database "'+ @DatabaseName +'" doesnt exists' 
 End
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 IF (@BackupType like 'Full')
 BEGIN 
 IF exists (select 1 from sys.databases where [name] = @DatabaseName)
 begin
 -- When database is part of Availability Group, run FULL Backup only on primary replica so that it makes DIFFERENTIAL Backups valid
 IF @is_DB_HADREnabled = 0 or @isPrimaryReplica = 1
 BEGIN
 -- ---------------------------------------------------------------------------------------
 -- creating directories if not available with server name and DB name
 -- ---------------------------------------------------------------------------------------
 BEGIN
 exec sp_configure 'show advanced options', 1;
 reconfigure;
 exec sp_configure 'xp_cmdshell', 1;
 reconfigure;
 BEGIN
 Select @xpcommand = ('if not exist "' + @BackupPath + '" mkdir ' + ('"' + @BackupPath + '"') );
 Exec xp_cmdshell @xpcommand;
 END
 exec sp_configure 'show advanced options', 1;
 reconfigure;
 exec sp_configure 'xp_cmdshell', 0;
 reconfigure;
 exec sp_configure 'show advanced options', 0;
 reconfigure;
 END
 -- Actual Backup command Begins---------------------------------------------------------------------------------------------------------------
 set @BackupType = 'Full_';
 set @BackupFilePath = (@BackupPath + @DatabaseName + '_' + @ServerName + '_' +@BackupType + @VersionNumber + @BackupDateTime + @FileExtension);
 BACKUP Database @DatabaseName TO DISK = @BackupFilePath with compression;
 Print 'Backup Created as '+ @BackupFilePath
 END
 ELSE Print 'Backup NOT Possible here! as this is not primary replica, Full Backup recommened to perform on primary to keep further DIFF Backups valid'
 end
 Else 
 -- Print 'Fullbackup has been already done today! ' + @BackupFilePath
 Print 'Database "'+ @DatabaseName +'" doesnt exists' 
 End
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 IF (@BackupType like 'Full_Copy' or @BackupType is null)
 BEGIN
 -- IF not exists (select * from msdb.dbo.backupset where database_name = @DatabaseName and cast (backup_finish_date as date) = cast (getdate () as date))
 IF exists (select 1 from sys.databases where [name] = @DatabaseName)
 begin
 -- When database is part of Availability Group, the FULL COPY_ONLY Backup can on only Preffered replica to avoid workload on Primary replica
 IF @IsPrefReplica = 1
 BEGIN
 -- ---------------------------------------------------------------------------------------
 -- creating directories if not available with server name and DB name
 -- ---------------------------------------------------------------------------------------
 BEGIN
 exec sp_configure 'show advanced options', 1;
 reconfigure;
 exec sp_configure 'xp_cmdshell', 1;
 reconfigure;
 BEGIN
 Select @xpcommand = ('if not exist "' + @BackupPath + '" mkdir ' + ('"' + @BackupPath + '"') );
 Exec xp_cmdshell @xpcommand;
 END
 exec sp_configure 'show advanced options', 1;
 reconfigure;
 exec sp_configure 'xp_cmdshell', 0;
 reconfigure;
 exec sp_configure 'show advanced options', 0;
 reconfigure;
 END
 -- Actual Backup command Begins---------------------------------------------------------------------------------------------------------------
 set @BackupType = 'Full_Copy_';
 set @BackupFilePath = (@BackupPath + @DatabaseName + '_' + @ServerName + '_' +@BackupType + @VersionNumber + @BackupDateTime + @FileExtension);
 BACKUP Database @DatabaseName TO DISK = @BackupFilePath with compression, copy_only;
 Print 'Backup Created as '+ @BackupFilePath
 END
 ELSE Print 'Its not preffered replica for the backup '
 end
 Else 
 -- Print 'Fullbackup has been done already for today! ' + @BackupFilePath
 Print 'Database "'+ @DatabaseName +'" doesnt exists' 
 End
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 IF @BackupType like 'Dif%'
 BEGIN
 IF exists (select 1 from sys.databases where [name] = @DatabaseName)
 begin
 -- When database is part of Availability Group, the FULL Backup must run on only primary replica so that it makes DIFFERENTIAL Backups valid
 IF @IsPrefReplica = 1
 BEGIN
 -- ---------------------------------------------------------------------------------------
 -- creating directories if not available with server name and DB name
 -- ---------------------------------------------------------------------------------------
 BEGIN
 exec sp_configure 'show advanced options', 1;
 reconfigure;
 exec sp_configure 'xp_cmdshell', 1;
 reconfigure;
 BEGIN
 Select @xpcommand = ('if not exist "' + @BackupPath + '" mkdir ' + ('"' + @BackupPath + '"') );
 Exec xp_cmdshell @xpcommand;
 END
 exec sp_configure 'show advanced options', 1;
 reconfigure;
 exec sp_configure 'xp_cmdshell', 0;
 reconfigure;
 exec sp_configure 'show advanced options', 0;
 reconfigure;
 END
 -- Actual Backup command Begins---------------------------------------------------------------------------------------------------------------
 set @BackupType = 'Dif_';
 set @BackupFilePath = (@BackupPath + @DatabaseName + '_' + @ServerName + '_' +@BackupType + @VersionNumber + @BackupDateTime + @FileExtension);
 BACKUP Database @DatabaseName TO DISK = @BackupFilePath with DIFFERENTIAL, compression;
 Print 'Backup Created as '+ @BackupFilePath
 END
 ELSE Print 'Its not preffered replica for the backup '
 end
 Else 
 -- Print 'Fullbackup has been done already for today! ' + @BackupFilePath
 Print 'Database "'+ @DatabaseName +'" doesnt exists' 
 End
 End
END

I want Full Backup everyday, Differential Backup every hour and Transnational Log Backup every 15 mins. My second concern, Is this a good practice?

The decision has to come from the business demands:
If Recovery point is concern you can have more frequent LOG backups to have closest point-in-time recovery possible.

If Recovery time is concern having daily FULL backup and Differential backups during the day would help us to do faster recovery.

Note: In case the Recovery model set to FULL, regardless of the Recovery object Time/Point you must perform LOG backups regularly

You can identity the recovery mode of the databases:

select name, recovery_model_desc from sys.databases 

• lili: Hi, Thank you so much. It is really helpful. I just confused in last line. "If Database Recovery model set to FULL, in any case we cannot avoid LOG backups" I understand from your answer that we always need to use the full backup for recovery since the differentian and Log backups both need Full back up first. The last sentence you mentioned If database recovery model set to full, can we set it to another thing? if yes, may you please explain why the advantage of that?
• Shekar Kola: Hello @lili, I mean, if your database recovery model is FULL (select name, recovery_model_desc from sys.databases ), you must take LOG backup regularly, otherwise will end-up with Log file growth issue. Thus, I mentioned it certainly because you may want NOT to do LOG backups, just define your strategy with FULL and 'DIFF'.
• lili: I see! I understand now. I will going to use your script. appreciated :)