Full DB Backup

--- This SP is written for weekly backup on Friday. So A scheduler should be created for this SP which will run in every friday & will take full backup. 

Alter PROCEDURE HRMS_TBD_FullDB_Backup_SP
 ---- WITH ENCRYPTION
AS
BEGIN
	DECLARE @MyFileName varchar(1000), @date varchar(100), @location varchar(200), @database varchar(100)


set @location = 'E:\DMS\'   --set location

set @database = 'HRMS_TBD'  ---- set database 

set @date = cast((datepart(YEAR,getdate())) as varchar) + '_'  + cast((datepart(MONTH,getdate())) as varchar)  + '_' 
		  + cast((datepart(DAY,getdate())) as varchar) 
		  
select @date

set @MyFileName = (SELECT @location+ @database+'_' + @date ) ---- set filename and location 

select @MyFileName


BACKUP DATABASE [HRMS_TBD] TO  DISK = @MyFileName WITH FORMAT, INIT,

MEDIANAME = @MyFileName,  NAME = @MyFileName, SKIP, NOREWIND, NOUNLOAD, No_COMPRESSION,  STATS = 10


END

Partial Log Backup

---- This SP will take partial Log backup & will append it to previous Full backup. So here filename should be same as "full backup file name".
---- For exmp : Let, previous full backup file name: HRMS_TBD_2020_9_18. Until next friday (2020-09-25), all  partial Log will append to "HRMS_TBD_2020_9_18" this file.
---- A scheduler should be created for this SP which will run everyday totake partial log

ALTER PROCEDURE HRMS_TBD_PARTIAL_LOG_Backup_SP
 ---- WITH ENCRYPTION
AS
BEGIN

DECLARE @dates TABLE(dt datetime)   
DECLARE @dateFrom datetime
DECLARE @dateTo datetime

SET @dateFrom = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) , 0);

SET @dateTo = DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

INSERT INTO @dates SELECT @dateFrom ;


WHILE(@dateFrom  < @dateTo)
BEGIN

   SELECT @dateFrom = DATEADD(day, 1,@dateFrom)

   INSERT INTO @dates 
   SELECT @dateFrom

END

declare @Friday_Date datetime

set @Friday_Date =  (select max(dt) from @dates where	datename(DW,dt) = 'friday' and dt <= getdate() )

select @Friday_Date AS Friday_Date


DECLARE @MyFileName varchar(1000), @date varchar(100), @location varchar(200), @database varchar(100)


set @location = 'E:\DMS\'   --set location

set @database = 'HRMS_TBD'          ---- set database 

set @date =  cast((datepart(YEAR,getdate())) as varchar)  
	+ '_' +  cast((datepart(MONTH,getdate())) as varchar)  
	+ '_' + cast((datepart(DAY,@Friday_Date)) as varchar) 


select @date

set @MyFileName = (SELECT @location+ @database+'_' + @date ) ---- set filename and location 

select @MyFileName AS FileName


BACKUP LOG [HRMS_TBD] TO  DISK = @MyFileName 

WITH NOFORMAT, NOINIT,  NAME = @MyFileName , SKIP, NOREWIND, NOUNLOAD,  STATS = 10

END