SQL SERVER DBA - Backup job script for Always on DB's

SQL SERVER DBA -  Backup job script for Always on server :

Below script for execute backups on secondary server.

---------------------------------------------------------------------------
User databases:
---------------------------------------------------------------------------

-- Backup Code start

declare
@ServerName nvarchar(256)  = @@SERVERNAME,
@RoleDesc nvarchar(60)

select @RoleDesc = a.role_desc
from sys.dm_hadr_availability_replica_states as a
join sys.availability_replicas as b
on b.replica_id = a.replica_id
where b.replica_server_name = @ServerName

if upper(isnull(@RoleDesc,'')) = 'SECONDARY'
begin
    --Run Backup--

declare @Excluded_DBs table
(
Seq_No int identity(1,1),
dbname varchar(100)
)

insert @Excluded_DBs select 'tempdb'
insert @Excluded_DBs select 'master'
insert @Excluded_DBs select 'model'
insert @Excluded_DBs select 'msdb'

declare
@Backup nvarchar(100) = 'BACKUP DATABASE ',
@ToDisk varchar(MAX) = ' TO ' ,
@BackupFileName1 varchar(max),
@BackupCount bigint,
@BackupPath nvarchar(500),
@BackupType varchar(5) = 'FULL',
@BackupExtenstion varchar(4) = '.BAK',
@BackupDrive varchar(100) = 'C:\Server02\Backup',
@BackupTime varchar(100),
@BackupDate varchar(100),
@BackupDay varchar(100)

declare
@With varchar(max) = ' WITH COPY_ONLY, INIT, NOFORMAT, NOREWIND, NOUNLOAD, SKIP, NAME=',
@DB_Name varchar(100),
@SQL varchar(max)


declare crsBackup cursor for
select name from sys.databases d
where d.state_desc = 'ONLINE'
and name not in (select dbname from @Excluded_DBs)
order by name
open crsBackup
fetch crsBackup into @DB_Name
while @@fetch_status = 0
begin
set @BackupPath = @BackupDrive
set @BackupTime = right('00' + convert(varchar,datepart(hour,getdate())),2) + '.' + right('00' + convert(varchar,datepart(minute,getdate())),2) + '-'
set @BackupDate = replace(convert(varchar,getdate(),105),'-','.') + @BackupExtenstion + ''''
set @BackupDay = LEFT(UPPER(datename(dw, getdate())), 3)

--files 1-4
set @BackupFileName1 = '''' + @BackupPath + '\' + @BackupType + '\' + @BackupDay + '\' + @DB_Name
set @BackupFileName1 = 'DISK=' + @BackupFileName1 + '_' + @BackupType + '_' + '1' + '_' + @BackupTime + @BackupDate


set @SQL = @Backup + @DB_Name + @ToDisk
   + @BackupFileName1
   + @With + ''''  + @DB_Name + ' Full Backup-' + @BackupTime + @BackupDate + ''

print @SQL
--exec(@SQL)

fetch crsBackup into @DB_Name
end
close crsBackup
deallocate crsBackup
end

--code end

----------------------------------------
SYSTEM Databases:
---------------------------------------
-- Backup Code start

declare
@ServerName nvarchar(256)  = @@SERVERNAME,
@RoleDesc nvarchar(60)

begin
    --Run Backup--

declare @Included_DBs table
(
Seq_No int identity(1,1),
dbname varchar(100)
)

insert @Included_DBs select 'master'
insert @Included_DBs select 'model'
insert @Included_DBs select 'msdb'

declare
@Backup nvarchar(100) = 'BACKUP DATABASE ',
@ToDisk varchar(MAX) = ' TO ' ,
@BackupFileName1 varchar(max),
@BackupCount bigint,
@BackupPath nvarchar(500),
@BackupType varchar(5) = 'FULL',
@BackupExtenstion varchar(4) = '.BAK',
@BackupDrive varchar(100) = 'C:\Server02\Backup',
@BackupTime varchar(100),
@BackupDate varchar(100),
@BackupDay varchar(100)

declare
@With varchar(max) = ' WITH INIT, NOFORMAT, NOREWIND, NOUNLOAD, SKIP,  COMPRESSION, NAME=',
@DB_Name varchar(100),
@SQL varchar(max),
@DB_Name1 varchar(100) = 'SQL'

declare crsBackup cursor for
select name from sys.databases d
where d.state_desc = 'ONLINE'
and name in (select dbname from @Included_DBs)
order by name
open crsBackup
fetch crsBackup into @DB_Name
while @@fetch_status = 0
begin
set @BackupPath = @BackupDrive
set @BackupTime = right('00' + convert(varchar,datepart(hour,getdate())),2) + '.' + right('00' + convert(varchar,datepart(minute,getdate())),2) + '-'
set @BackupDate = replace(convert(varchar,getdate(),105),'-','.') + @BackupExtenstion + ''''
set @BackupDay = LEFT(UPPER(datename(dw, getdate())), 3)

set @BackupFileName1 = '''' + @BackupPath + '\' + @BackupType + '\'+ @BackupDay + '\' + @DB_Name
set @BackupFileName1 = 'DISK=' + @BackupFileName1 + '_' + @BackupType + '_' + '1' + '_' + @BackupTime + @BackupDate

set @SQL = @Backup + @DB_Name + @ToDisk
   + @BackupFileName1
   + @With + ''''  + @DB_Name + ' Full Backup-' + @BackupTime + @BackupDate + ''

--print @SQL
exec(@SQL)

fetch crsBackup into @DB_Name
end
close crsBackup
deallocate crsBackup
end

--code end

--------------------------------------
LOG Backup:
-------------------------------------

--16/08/2016-Modified to backup log into accross four split T-Log Folders instead--

declare
@ServerName nvarchar(256)  = @@SERVERNAME,
@RoleDesc nvarchar(60)

select @RoleDesc = a.role_desc
from sys.dm_hadr_availability_replica_states as a
join sys.availability_replicas as b
on b.replica_id = a.replica_id
where b.replica_server_name = @ServerName

if upper(isnull(@RoleDesc,'')) = 'SECONDARY'
begin
    --Run Backup--

declare @Excluded_DBs table
(
Seq_No int identity(1,1),
dbname varchar(100)
)

insert @Excluded_DBs select 'tempdb'
insert @Excluded_DBs select 'master'
insert @Excluded_DBs select 'model'
insert @Excluded_DBs select 'msdb'

declare
@Backup nvarchar(100) = 'BACKUP LOG ',
@ToDisk varchar(MAX) = ' TO ' ,
@BackupFileName1 varchar(max),
@BackupFileName2 varchar(max),
@BackupFileName3 varchar(max),
@BackupFileName4 varchar(max),
@BackupCount bigint,
@BackupPath nvarchar(500),
@BackupType varchar(5) = 'TLOG',
--@BackupType1 varchar(5) = 'TLOG',
@BackupExtenstion varchar(4) = '.TRN',
@BackupDrive varchar(100) = 'C:\Server02\Backup',
@BackupTime varchar(100),
@BackupDate varchar(100),
@BackupDay varchar(100)

declare
@With varchar(max) = ' WITH INIT, NOFORMAT, NOREWIND, COMPRESSION, NOUNLOAD, SKIP, NAME=',
@DB_Name varchar(100),
@SQL varchar(max),
@DB_Name1 varchar(100) = 'SQL'

declare crsBackup cursor for
select name from sys.databases d
where d.state_desc = 'ONLINE'
and name not in (select dbname from @Excluded_DBs)
order by name
open crsBackup
fetch crsBackup into @DB_Name
while @@fetch_status = 0
begin
set @BackupPath = @BackupDrive
set @BackupTime = right('00' + convert(varchar,datepart(hour,getdate())),2) + '.' + right('00' + convert(varchar,datepart(minute,getdate())),2) + '-'
set @BackupDate = replace(convert(varchar,getdate(),105),'-','.') + @BackupExtenstion + ''''
set @BackupDay = LEFT(UPPER(datename(dw, getdate())), 3)

--files 1-4
set @BackupFileName1 = '''' + @BackupPath + '\' + @BackupType + '\' + @BackupDay + '\' + @DB_Name
set @BackupFileName1 = 'DISK=' + @BackupFileName1 + '_' + @BackupType + '_' + '1' + '_' + @BackupTime + @BackupDate

set @SQL = @Backup + @DB_Name + @ToDisk
   + @BackupFileName1 +    
   + @With + ''''  + @DB_Name + ' TLOG Backup-' + @BackupTime + @BackupDate + ''

exec(@SQL)
--print(@SQL)

fetch crsBackup into @DB_Name
end
close crsBackup
deallocate crsBackup
end

----------------------------------------------