Considering following points may help you to plan backup schedule properly:
You can schedule your backups with following methods other than Maintenance plan
- Custom script (
stored procedure) - SQL Agent
- 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
Contain full copy of the database including all data files (in case file groups in-place)
Consider the storage for full backup as it cannot be less than 15 to 20% of whole database size even with compression enabled
- Recovery would be easy and faster compare to
LOG
- 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
Contain only the changes that occurred after the last full backup
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
Recovery cannot be possible without having FULL backup available
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
Contain only the changes that occurred after the last full backup
Consider only when DATABASE RECOVERY set to FULL, otherwise no need (not possible)
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:
- Create JOB each backup type i.e. (JOB1-Full, JOB2-Differential, JOB3- Log) with desired schedule
- Every database goes with different step in the JOB
- 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
