-- Detail : Main SP to take the database FULL & DIFF backup
[Main_Backup_Sp]
( @Batch Varchar(50)
)As
DECLARE @SQL NVarchar(4000),@V_File_Full NVarchar(500),@V_File_Diff Varchar(100)
--Cursor Variable declaration
Declare @DB_Name Varchar(255),@Archive_90Days Varchar(255),@Archive_15Days Varchar(255),@Archive_15_File_Path Varchar(255),@V_BackupType Varchar(255),@BackupFileName Varchar(4000),@Rowid int
Declare @Sun Varchar(10),@Mon Varchar(10),@Tue Varchar(10),@Wed Varchar(10),@Thu Varchar(10),@Fri Varchar(10),@Sat Varchar(10), @isExists INT ,@isExists2 INT
DECLARE @FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@Bkp_Ret Int,@Bkp_Srv_Ret Int, @Required_Bkp_Copy Bit,@Staging_Folder Varchar(255),@Live_Bkp_Path Varchar(255),@Live_File_Name Varchar(255)
DECLARE @tbl TABLE
(
ID INT IDENTITY(1,1),
DBNAME NVARCHAR(1000),
[FileName] NVARCHAR(1000)
)
INSERT INTO @tbl
SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles ST INNER JOIN Sys.databases SB on ST.dbid = sb.database_id
where sb.NAme not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
And FileName LIKE '%ldf' and sb.state = 0 And state_desc = 'ONLINE'
DECLARE CURSOR__MAIN CURSOR FOR
Select [DB_Name]
,Archive_90Days
,Archive_15Days
,Bkp_Retention_15Days
,Bkp_Retention_90Days
,Required_Bkp_Copy
,Staging_Folder
From [Bkp_Config_Tbl]
Where Required_Backup = 1
And Backup_Batch = @Batch
And [DB_Name] In(Select Name from Sys.databases Where state_desc = 'ONLINE')
OPEN CURSOR__MAIN
FETCH NEXT FROM CURSOR__MAIN INTO @DB_Name,@Archive_90Days,@Archive_15Days,@Bkp_Ret,@Bkp_Srv_Ret,@Required_Bkp_Copy,@Staging_Folder
WHILE @@FETCH_STATUS=0
BEGIN
--###################################### CRSR START ###############
Print '-- ==================== Database Name : '+@DB_Name+' ==================== (Start Time:'+Convert(Varchar(25),Getdate())+')'
-- =====================================================
-- VALIDATION FOR ==> Database ONLINE / OFFLINE
-- =====================================================
If Exists(Select Name from sys.databases Where [Name] = @DB_Name And state_desc = 'OFFLINE' )
Begin
Print 'Error:-Database '+@DB_Name+' is in OFFLINE mode.'
Insert Into Backup_Error_Log([DB_Name],[SP_Name],Error_Type,Error_Desc)
Values (@DB_Name,OBJECT_NAME(@@PROCID),'Execution Error','Error:-Database '+@DB_Name+' is in OFFLINE mode.')
Break
End
-- =====================================================
-- VALIDATION FOR ==> Database existance
-- =====================================================
If Not Exists(Select Name from sys.databases Where [Name] = @DB_Name)
Begin
Print 'Error:-Database '+@DB_Name+' is not exists in'+@@SERVERNAME+' server.'
Insert Into Backup_Error_Log([DB_Name],[SP_Name],Error_Type,Error_Desc)
Values (@DB_Name,OBJECT_NAME(@@PROCID),'Execution Error','Error:-Database '+@DB_Name+' is not exists in'+@@SERVERNAME+' server.')
Break
End
IF(ISNULL(@Staging_Folder,'')='')
Begin
Print 'Alert:-Staging_Folder column should not be empty in "Bkp_Config_Tbl" table for database '+@DB_Name
break
End
IF(Right(@Staging_Folder,1)!='\')
Begin
Set @Staging_Folder = @Staging_Folder+'\'
End
Set @V_BackupType = ''
Select @V_BackupType =
Case DATEPART(DW,GETDATE())
When 1 Then [Bkp_Sunday]
When 2 Then [Bkp_Monday]
When 3 Then [Bkp_Tuesday]
When 4 Then [Bkp_Wednesday]
When 5 Then [Bkp_Thursday]
When 6 Then [Bkp_Friday]
When 7 Then [Bkp_Saturday]
End
From [Bkp_Config_Tbl]
Where [DB_Name] = @DB_Name And Required_Backup = 1
Print ' Backup Type - '+@V_BackupType
If (@V_BackupType IN('FULL','DIFF'))
BEGIN
INSERT INTO [Backup_History] ([DB_Name],[Bkp_Start_Time]) VALUES(@DB_Name,GETDATE())
Select @Rowid = Max(ROWID) From Backup_History
END
-- ==============================================
-- Clean the database backup folder
-- Added conditional check for previous error
-- ==============================================
IF NOT EXISTS(Select Top 1 * from [Backup_History]
Where [DB_Name] = @DB_Name
And Bkp_Start_Time Between Dateadd(DAY,-15,Getdate()) And Dateadd(MINUTE,-120,Getdate())
And Copy_To_15_Days = 2
)
BEGIN
--Print 'EMPTY FOLDER CODE'
Exec [Empty_Folder] @Staging_Folder
END
-- ==============================================
-- B A C K U P C O N D I T I O N A L ==> FULL OR DIFF?
-- ==============================================
If (@V_BackupType = 'DIFF')
BEGIN
-- ==============================================
-- Update backup status -Before
-- ==============================================
--Update the history log table
Update Backup_History Set Backup_Status = 'Backup initiated'
Where ROWID = @Rowid
-- ==============================================
-- Differential compressed backup
-- ==============================================
--IF (@Required_Bkp_Copy = 1)
Set @Live_Bkp_Path = @Staging_Folder
--Else
-- Set @Live_Bkp_Path = @Archive_90Days
SET @Live_File_Name = REPLACE(CONVERT(varchar,GETDATE(), 112),'/','_')+REPLACE(LEFT(CONVERT(varchar,GETDATE(), 114),5),':','')+'_'+
Replace(@@SERVERNAME,'\','_')+'_'+@DB_Name+'_'+'DIFF.BAK'
SET @V_File_Diff = @Live_Bkp_Path + @Live_File_Name
Set @SQL = 'BACKUP DATABASE '+ @DB_Name + ' TO DISK = N'+Quotename(@V_File_Diff,'''')+'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'+Quotename(@DB_Name+'Diff Database Backup','''')+', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
Print @SQL
EXEC SP_EXECUTESQL @SQL
-- Set 25 Days folder - complete file path
IF(Right(@Archive_15Days,1)!='\')
Set @Archive_15_File_Path = @Archive_15Days+'\'+@Live_File_Name
Else
Set @Archive_15_File_Path = @Archive_15Days+@Live_File_Name
-- ==============================================
-- Update backup status - After
-- ==============================================
--Update the history log table
Update Backup_History Set
Backup_File_Name = @Live_File_Name,
DB_Backup = 1 ,
File_Existance = 'Available',
Backup_Status = 'Success',
Bkp_Finish_Time = GETDATE(),
Bkp_Retention_15Days = Dateadd(Day,1,Dateadd(day,@Bkp_Ret,GETDATE())),
Bkp_Retention_90Days = Dateadd(Day,1,Dateadd(day,@Bkp_Srv_Ret,GETDATE()))
Where ROWID = @Rowid
-- ==============================================
-- Copy file to Archive folder
-- ==============================================
IF (@Required_Bkp_Copy = 1)
Begin
--Print 'Copy to Archive folder (82 Days) - '+@Archive_90Days
--Exec [File_Copy] @V_File_Diff,@Archive_90Days
--Check file existance
Set @isExists = 0
exec master.dbo.xp_fileexist @V_File_Diff, @isExists OUTPUT
IF(@isExists = 1)
Begin
Print 'Copy to Archive folder (25 Days) - '+@Archive_15Days
Exec [File_Copy] @V_File_Diff,@Archive_15Days
-- ########################### 18 OCT 2016 #############
--Check file existance @ Backup server 25 Days folder
Set @isExists2 = 0
exec master.dbo.xp_fileexist @Archive_15_File_Path, @isExists2 OUTPUT
IF(@isExists2 = 1)
Begin
--Update the history log table
Update Backup_History
Set
Copy_To_15_Days = 1
Where ROWID = @Rowid
End
Else
Begin
Update Backup_History
Set
Copy_To_15_Days = 2,
Remarks = ISNULL(Remarks,'')+';'+'Error:Backup is not copied to 25 Days Folder'
Where ROWID = @Rowid
And Copy_To_15_Days = 0
End
-- ###########################
End
End
Update A
Set A.Bkp_Size_MB = B.compressed_backup_size_MB
From DBA_Admin.dbo.Backup_History A Inner Join DBA_Admin.dbo.[Backup_Details] B
On A.Backup_File_Name = Right(B.physical_device_name,Len(A.Backup_File_Name))
Where A.Backup_File_Name = @Live_File_Name
END
If (@V_BackupType = 'FULL')
BEGIN
-- ==============================================
--DB shrink before FULL backup
-- ==============================================
Print 'Shrink start - '+@DB_Name
SELECT @FileName=[FileName] FROM @tbl where DBNAME = @DB_Name
SELECT @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DB_Name
SELECT @SQL= N'USE ['+ @DB_Name+']'+CHAR(10)
+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DB_Name+'] SET RECOVERY SIMPLE WITH NO_WAIT' ELSE N''END+CHAR(10)
+N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)
+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DB_Name+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END
Print(@SQL)
EXEC SP_EXECUTESQL @SQL
Print 'Shrink finished - '+@DB_Name
--Update the history log table
Update Backup_History Set DB_Shrink = 1 Where ROWID = @Rowid
-- ==============================================
-- Update backup status -Before
-- ==============================================
--Update the history log table
Update Backup_History Set Backup_Status = 'Backup initiated'
Where ROWID = @Rowid
-- ==============================================
-- Full compressed backup
-- ==============================================
--IF (@Required_Bkp_Copy = 1)
Set @Live_Bkp_Path = @Staging_Folder
--Else
-- Set @Live_Bkp_Path = @Archive_90Days
SET @Live_File_Name = REPLACE(CONVERT(varchar,GETDATE(), 112),'/','_')+REPLACE(LEFT(CONVERT(varchar,GETDATE(), 114),5),':','')+'_'+
Replace(@@SERVERNAME,'\','_')+'_'+@DB_Name+'_'+'FULL.BAK'
SET @V_File_Full = @Live_Bkp_Path + @Live_File_Name
Set @SQL = 'BACKUP DATABASE '+ @DB_Name + ' TO DISK = N'+Quotename(@V_File_Full,'''')+'
WITH NOFORMAT, NOINIT, NAME = N'+Quotename(@DB_Name+'Full Database Backup','''')+', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
Print @SQL
EXEC SP_EXECUTESQL @SQL
-- Set 25 Days folder - complete file path
IF(Right(@Archive_15Days,1)!='\')
Set @Archive_15_File_Path = @Archive_15Days+'\'+@Live_File_Name
Else
Set @Archive_15_File_Path = @Archive_15Days+@Live_File_Name
-- ==============================================
-- Update backup status - After (Update the history log table)
-- ==============================================
--Update the history log table
Update Backup_History
Set
Backup_File_Name = @Live_File_Name,
DB_Backup = 1,
File_Existance = 'Available',
Backup_Status = 'Success',
Bkp_Finish_Time = GETDATE(),
Bkp_Retention_15Days = Dateadd(Day,1,Dateadd(day,@Bkp_Ret,GETDATE())),
[Bkp_Retention_90Days] = Dateadd(Day,1,Dateadd(day,@Bkp_Srv_Ret,GETDATE()))
Where ROWID = @Rowid
-- ==============================================
-- Copy file to Archive folder
-- ==============================================
IF (@Required_Bkp_Copy = 1)
Begin
--Print 'Copying to Archive_90Days folder'
--Exec [File_Copy] @V_File_Full,@Archive_90Days
--Check file existance
Set @isExists = 0
exec master.dbo.xp_fileexist @V_File_Full, @isExists OUTPUT
IF(@isExists = 1)
Begin
Print 'Copying to Archive_15Days folder'
Exec [File_Copy] @V_File_Full,@Archive_15Days
-- ########################### 18 OCT 2016 #############
--Check file existance @ Backup server 25 Days folder
Set @isExists2 = 0
exec master.dbo.xp_fileexist @Archive_15_File_Path, @isExists2 OUTPUT
IF(@isExists2 = 1)
Begin
--Update the history log table
Update Backup_History
Set
Copy_To_15_Days = 1
Where ROWID = @Rowid
End
Else
Begin
Update Backup_History
Set
Copy_To_15_Days = 2,
Remarks = ISNULL(Remarks,'')+';'+'Error:Backup is not copied to 25 Days Folder'
Where ROWID = @Rowid
And Copy_To_15_Days = 0
End
-- ###########################
End
Else
Begin
Print 'Error:-Database '+@DB_Name+' - Backup file not exists.'+@V_File_Full
Insert Into Backup_Error_Log([DB_Name],[SP_Name],Error_Type,Error_Desc)
Values (@DB_Name,OBJECT_NAME(@@PROCID),'Backup file','Error:-Database '+@DB_Name+' - Backup file not exists - '+@V_File_Full)
End
End
-- Update the backup size
Update A
Set A.Bkp_Size_MB = B.compressed_backup_size_MB
From DBA_Admin.dbo.Backup_History A Inner Join DBA_Admin.dbo.[Backup_Details] B
On A.Backup_File_Name = Right(B.physical_device_name,Len(A.Backup_File_Name))
Where A.Backup_File_Name = @Live_File_Name
END
Update DBA_Admin.dbo.Backup_History
Set Copy_To_15_Days = 2,
Remarks = ISNULL(Remarks,'')+';'+'Error:Backup is not copied to 25 Days Folder'
Where ROWID = @Rowid And Copy_To_15_Days = 0 And File_Existance = 'Available'
--###################################### CRSR END###############
FETCH NEXT FROM CURSOR__MAIN INTO @DB_Name,@Archive_90Days,@Archive_15Days,@Bkp_Ret,@Bkp_Srv_Ret,@Required_Bkp_Copy,@Staging_Folder
END
CLOSE CURSOR__MAIN
DEALLOCATE CURSOR__MAIN
/*
Select * from [Bkp_Config_Tbl]
;yadseuT_pkB;yadnoM_pkB;yadnuS_pkB;syaD09_noitneteR_pkB;syaD51_noitneteR_pkB;syaD51_evihcrA;syaD09_evihcrA;redloF_gnigatS;evirD_gnigatS;ypoC_pkB_deriuqeR;pukcaB_deriuqeR;ledoM_yrevoceR_qeR;maeT;emaN_noitacilppA;emaN_BD::]lbT_gifnoC_pkB[
;syaD51_noitneteR_pkB;emiT_hsiniF_pkB;emiT_tratS_pkB;emaN_eliF_pukcaB;emaN_BD;DIWOR::tsiH_pukcaBepaT_deriuqeR;tnemegagnE;ycneuqerF_pkB;etaDetadpU;etaDetaerC;setoN_laicepS;sgnimiT_pukcaB;hctaB_pukcaB;yadrutaS_pkB;yadirF_pkB;yadsruhT_pkB;yadsendeW_pkB
;syaD_09_morF_deteleD;syaD_09_oT_ypoC;syaD_51_oT_ypoC;ecnatsixE_eliF;BM_eziS_pkB;syaD09_noitneteR_pkB
Hist
;syaD_09_morF_deteleD;syaD_09_oT_ypoC;syaD_51_oT_ypoC;ecnatsixE_eliF;BM_eziS_pkB;syaD09_noitneteR_pkB;syaD51_noitneteR_pkB;emiT_hsiniF_pkB;emiT_tratS_pkB;emaN_eliF_pukcaB;emaN_BD;DIWOR::tsiH_pukcaB
DB_Shrink;DB_Backup;Copy_To_Tape;Backup_Status;Remarks;Manual_Comments;Ref_Rowid_Manual;Tape_Upd_Date;
*/
GO
[Main_Backup_Sp]
( @Batch Varchar(50)
)As
DECLARE @SQL NVarchar(4000),@V_File_Full NVarchar(500),@V_File_Diff Varchar(100)
--Cursor Variable declaration
Declare @DB_Name Varchar(255),@Archive_90Days Varchar(255),@Archive_15Days Varchar(255),@Archive_15_File_Path Varchar(255),@V_BackupType Varchar(255),@BackupFileName Varchar(4000),@Rowid int
Declare @Sun Varchar(10),@Mon Varchar(10),@Tue Varchar(10),@Wed Varchar(10),@Thu Varchar(10),@Fri Varchar(10),@Sat Varchar(10), @isExists INT ,@isExists2 INT
DECLARE @FileName NVARCHAR(1000),@RecoveryModel NVARCHAR(1000),@Bkp_Ret Int,@Bkp_Srv_Ret Int, @Required_Bkp_Copy Bit,@Staging_Folder Varchar(255),@Live_Bkp_Path Varchar(255),@Live_File_Name Varchar(255)
DECLARE @tbl TABLE
(
ID INT IDENTITY(1,1),
DBNAME NVARCHAR(1000),
[FileName] NVARCHAR(1000)
)
INSERT INTO @tbl
SELECT DB_NAME(DbID),st.NAme from sys.sysaltfiles ST INNER JOIN Sys.databases SB on ST.dbid = sb.database_id
where sb.NAme not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')
And FileName LIKE '%ldf' and sb.state = 0 And state_desc = 'ONLINE'
DECLARE CURSOR__MAIN CURSOR FOR
Select [DB_Name]
,Archive_90Days
,Archive_15Days
,Bkp_Retention_15Days
,Bkp_Retention_90Days
,Required_Bkp_Copy
,Staging_Folder
From [Bkp_Config_Tbl]
Where Required_Backup = 1
And Backup_Batch = @Batch
And [DB_Name] In(Select Name from Sys.databases Where state_desc = 'ONLINE')
OPEN CURSOR__MAIN
FETCH NEXT FROM CURSOR__MAIN INTO @DB_Name,@Archive_90Days,@Archive_15Days,@Bkp_Ret,@Bkp_Srv_Ret,@Required_Bkp_Copy,@Staging_Folder
WHILE @@FETCH_STATUS=0
BEGIN
--###################################### CRSR START ###############
Print '-- ==================== Database Name : '+@DB_Name+' ==================== (Start Time:'+Convert(Varchar(25),Getdate())+')'
-- =====================================================
-- VALIDATION FOR ==> Database ONLINE / OFFLINE
-- =====================================================
If Exists(Select Name from sys.databases Where [Name] = @DB_Name And state_desc = 'OFFLINE' )
Begin
Print 'Error:-Database '+@DB_Name+' is in OFFLINE mode.'
Insert Into Backup_Error_Log([DB_Name],[SP_Name],Error_Type,Error_Desc)
Values (@DB_Name,OBJECT_NAME(@@PROCID),'Execution Error','Error:-Database '+@DB_Name+' is in OFFLINE mode.')
Break
End
-- =====================================================
-- VALIDATION FOR ==> Database existance
-- =====================================================
If Not Exists(Select Name from sys.databases Where [Name] = @DB_Name)
Begin
Print 'Error:-Database '+@DB_Name+' is not exists in'+@@SERVERNAME+' server.'
Insert Into Backup_Error_Log([DB_Name],[SP_Name],Error_Type,Error_Desc)
Values (@DB_Name,OBJECT_NAME(@@PROCID),'Execution Error','Error:-Database '+@DB_Name+' is not exists in'+@@SERVERNAME+' server.')
Break
End
IF(ISNULL(@Staging_Folder,'')='')
Begin
Print 'Alert:-Staging_Folder column should not be empty in "Bkp_Config_Tbl" table for database '+@DB_Name
break
End
IF(Right(@Staging_Folder,1)!='\')
Begin
Set @Staging_Folder = @Staging_Folder+'\'
End
Set @V_BackupType = ''
Select @V_BackupType =
Case DATEPART(DW,GETDATE())
When 1 Then [Bkp_Sunday]
When 2 Then [Bkp_Monday]
When 3 Then [Bkp_Tuesday]
When 4 Then [Bkp_Wednesday]
When 5 Then [Bkp_Thursday]
When 6 Then [Bkp_Friday]
When 7 Then [Bkp_Saturday]
End
From [Bkp_Config_Tbl]
Where [DB_Name] = @DB_Name And Required_Backup = 1
Print ' Backup Type - '+@V_BackupType
If (@V_BackupType IN('FULL','DIFF'))
BEGIN
INSERT INTO [Backup_History] ([DB_Name],[Bkp_Start_Time]) VALUES(@DB_Name,GETDATE())
Select @Rowid = Max(ROWID) From Backup_History
END
-- ==============================================
-- Clean the database backup folder
-- Added conditional check for previous error
-- ==============================================
IF NOT EXISTS(Select Top 1 * from [Backup_History]
Where [DB_Name] = @DB_Name
And Bkp_Start_Time Between Dateadd(DAY,-15,Getdate()) And Dateadd(MINUTE,-120,Getdate())
And Copy_To_15_Days = 2
)
BEGIN
--Print 'EMPTY FOLDER CODE'
Exec [Empty_Folder] @Staging_Folder
END
-- ==============================================
-- B A C K U P C O N D I T I O N A L ==> FULL OR DIFF?
-- ==============================================
If (@V_BackupType = 'DIFF')
BEGIN
-- ==============================================
-- Update backup status -Before
-- ==============================================
--Update the history log table
Update Backup_History Set Backup_Status = 'Backup initiated'
Where ROWID = @Rowid
-- ==============================================
-- Differential compressed backup
-- ==============================================
--IF (@Required_Bkp_Copy = 1)
Set @Live_Bkp_Path = @Staging_Folder
--Else
-- Set @Live_Bkp_Path = @Archive_90Days
SET @Live_File_Name = REPLACE(CONVERT(varchar,GETDATE(), 112),'/','_')+REPLACE(LEFT(CONVERT(varchar,GETDATE(), 114),5),':','')+'_'+
Replace(@@SERVERNAME,'\','_')+'_'+@DB_Name+'_'+'DIFF.BAK'
SET @V_File_Diff = @Live_Bkp_Path + @Live_File_Name
Set @SQL = 'BACKUP DATABASE '+ @DB_Name + ' TO DISK = N'+Quotename(@V_File_Diff,'''')+'
WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'+Quotename(@DB_Name+'Diff Database Backup','''')+', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
Print @SQL
EXEC SP_EXECUTESQL @SQL
-- Set 25 Days folder - complete file path
IF(Right(@Archive_15Days,1)!='\')
Set @Archive_15_File_Path = @Archive_15Days+'\'+@Live_File_Name
Else
Set @Archive_15_File_Path = @Archive_15Days+@Live_File_Name
-- ==============================================
-- Update backup status - After
-- ==============================================
--Update the history log table
Update Backup_History Set
Backup_File_Name = @Live_File_Name,
DB_Backup = 1 ,
File_Existance = 'Available',
Backup_Status = 'Success',
Bkp_Finish_Time = GETDATE(),
Bkp_Retention_15Days = Dateadd(Day,1,Dateadd(day,@Bkp_Ret,GETDATE())),
Bkp_Retention_90Days = Dateadd(Day,1,Dateadd(day,@Bkp_Srv_Ret,GETDATE()))
Where ROWID = @Rowid
-- ==============================================
-- Copy file to Archive folder
-- ==============================================
IF (@Required_Bkp_Copy = 1)
Begin
--Print 'Copy to Archive folder (82 Days) - '+@Archive_90Days
--Exec [File_Copy] @V_File_Diff,@Archive_90Days
--Check file existance
Set @isExists = 0
exec master.dbo.xp_fileexist @V_File_Diff, @isExists OUTPUT
IF(@isExists = 1)
Begin
Print 'Copy to Archive folder (25 Days) - '+@Archive_15Days
Exec [File_Copy] @V_File_Diff,@Archive_15Days
-- ########################### 18 OCT 2016 #############
--Check file existance @ Backup server 25 Days folder
Set @isExists2 = 0
exec master.dbo.xp_fileexist @Archive_15_File_Path, @isExists2 OUTPUT
IF(@isExists2 = 1)
Begin
--Update the history log table
Update Backup_History
Set
Copy_To_15_Days = 1
Where ROWID = @Rowid
End
Else
Begin
Update Backup_History
Set
Copy_To_15_Days = 2,
Remarks = ISNULL(Remarks,'')+';'+'Error:Backup is not copied to 25 Days Folder'
Where ROWID = @Rowid
And Copy_To_15_Days = 0
End
-- ###########################
End
End
Update A
Set A.Bkp_Size_MB = B.compressed_backup_size_MB
From DBA_Admin.dbo.Backup_History A Inner Join DBA_Admin.dbo.[Backup_Details] B
On A.Backup_File_Name = Right(B.physical_device_name,Len(A.Backup_File_Name))
Where A.Backup_File_Name = @Live_File_Name
END
If (@V_BackupType = 'FULL')
BEGIN
-- ==============================================
--DB shrink before FULL backup
-- ==============================================
Print 'Shrink start - '+@DB_Name
SELECT @FileName=[FileName] FROM @tbl where DBNAME = @DB_Name
SELECT @RecoveryModel = recovery_model_desc FROM sys.databases where name = @DB_Name
SELECT @SQL= N'USE ['+ @DB_Name+']'+CHAR(10)
+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DB_Name+'] SET RECOVERY SIMPLE WITH NO_WAIT' ELSE N''END+CHAR(10)
+N'DBCC SHRINKFILE('+@FileName+',1)'+CHAR(10)
+CASE WHEN @RecoveryModel <> N'SIMPLE' THEN N'ALTER DATABASE ['+@DB_Name+'] SET RECOVERY '+@RecoveryModel+N' WITH NO_WAIT'+CHAR(10) ELSE N'' END
Print(@SQL)
EXEC SP_EXECUTESQL @SQL
Print 'Shrink finished - '+@DB_Name
--Update the history log table
Update Backup_History Set DB_Shrink = 1 Where ROWID = @Rowid
-- ==============================================
-- Update backup status -Before
-- ==============================================
--Update the history log table
Update Backup_History Set Backup_Status = 'Backup initiated'
Where ROWID = @Rowid
-- ==============================================
-- Full compressed backup
-- ==============================================
--IF (@Required_Bkp_Copy = 1)
Set @Live_Bkp_Path = @Staging_Folder
--Else
-- Set @Live_Bkp_Path = @Archive_90Days
SET @Live_File_Name = REPLACE(CONVERT(varchar,GETDATE(), 112),'/','_')+REPLACE(LEFT(CONVERT(varchar,GETDATE(), 114),5),':','')+'_'+
Replace(@@SERVERNAME,'\','_')+'_'+@DB_Name+'_'+'FULL.BAK'
SET @V_File_Full = @Live_Bkp_Path + @Live_File_Name
Set @SQL = 'BACKUP DATABASE '+ @DB_Name + ' TO DISK = N'+Quotename(@V_File_Full,'''')+'
WITH NOFORMAT, NOINIT, NAME = N'+Quotename(@DB_Name+'Full Database Backup','''')+', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10'
Print @SQL
EXEC SP_EXECUTESQL @SQL
-- Set 25 Days folder - complete file path
IF(Right(@Archive_15Days,1)!='\')
Set @Archive_15_File_Path = @Archive_15Days+'\'+@Live_File_Name
Else
Set @Archive_15_File_Path = @Archive_15Days+@Live_File_Name
-- ==============================================
-- Update backup status - After (Update the history log table)
-- ==============================================
--Update the history log table
Update Backup_History
Set
Backup_File_Name = @Live_File_Name,
DB_Backup = 1,
File_Existance = 'Available',
Backup_Status = 'Success',
Bkp_Finish_Time = GETDATE(),
Bkp_Retention_15Days = Dateadd(Day,1,Dateadd(day,@Bkp_Ret,GETDATE())),
[Bkp_Retention_90Days] = Dateadd(Day,1,Dateadd(day,@Bkp_Srv_Ret,GETDATE()))
Where ROWID = @Rowid
-- ==============================================
-- Copy file to Archive folder
-- ==============================================
IF (@Required_Bkp_Copy = 1)
Begin
--Print 'Copying to Archive_90Days folder'
--Exec [File_Copy] @V_File_Full,@Archive_90Days
--Check file existance
Set @isExists = 0
exec master.dbo.xp_fileexist @V_File_Full, @isExists OUTPUT
IF(@isExists = 1)
Begin
Print 'Copying to Archive_15Days folder'
Exec [File_Copy] @V_File_Full,@Archive_15Days
-- ########################### 18 OCT 2016 #############
--Check file existance @ Backup server 25 Days folder
Set @isExists2 = 0
exec master.dbo.xp_fileexist @Archive_15_File_Path, @isExists2 OUTPUT
IF(@isExists2 = 1)
Begin
--Update the history log table
Update Backup_History
Set
Copy_To_15_Days = 1
Where ROWID = @Rowid
End
Else
Begin
Update Backup_History
Set
Copy_To_15_Days = 2,
Remarks = ISNULL(Remarks,'')+';'+'Error:Backup is not copied to 25 Days Folder'
Where ROWID = @Rowid
And Copy_To_15_Days = 0
End
-- ###########################
End
Else
Begin
Print 'Error:-Database '+@DB_Name+' - Backup file not exists.'+@V_File_Full
Insert Into Backup_Error_Log([DB_Name],[SP_Name],Error_Type,Error_Desc)
Values (@DB_Name,OBJECT_NAME(@@PROCID),'Backup file','Error:-Database '+@DB_Name+' - Backup file not exists - '+@V_File_Full)
End
End
-- Update the backup size
Update A
Set A.Bkp_Size_MB = B.compressed_backup_size_MB
From DBA_Admin.dbo.Backup_History A Inner Join DBA_Admin.dbo.[Backup_Details] B
On A.Backup_File_Name = Right(B.physical_device_name,Len(A.Backup_File_Name))
Where A.Backup_File_Name = @Live_File_Name
END
Update DBA_Admin.dbo.Backup_History
Set Copy_To_15_Days = 2,
Remarks = ISNULL(Remarks,'')+';'+'Error:Backup is not copied to 25 Days Folder'
Where ROWID = @Rowid And Copy_To_15_Days = 0 And File_Existance = 'Available'
--###################################### CRSR END###############
FETCH NEXT FROM CURSOR__MAIN INTO @DB_Name,@Archive_90Days,@Archive_15Days,@Bkp_Ret,@Bkp_Srv_Ret,@Required_Bkp_Copy,@Staging_Folder
END
CLOSE CURSOR__MAIN
DEALLOCATE CURSOR__MAIN
/*
Select * from [Bkp_Config_Tbl]
;yadseuT_pkB;yadnoM_pkB;yadnuS_pkB;syaD09_noitneteR_pkB;syaD51_noitneteR_pkB;syaD51_evihcrA;syaD09_evihcrA;redloF_gnigatS;evirD_gnigatS;ypoC_pkB_deriuqeR;pukcaB_deriuqeR;ledoM_yrevoceR_qeR;maeT;emaN_noitacilppA;emaN_BD::]lbT_gifnoC_pkB[
;syaD51_noitneteR_pkB;emiT_hsiniF_pkB;emiT_tratS_pkB;emaN_eliF_pukcaB;emaN_BD;DIWOR::tsiH_pukcaBepaT_deriuqeR;tnemegagnE;ycneuqerF_pkB;etaDetadpU;etaDetaerC;setoN_laicepS;sgnimiT_pukcaB;hctaB_pukcaB;yadrutaS_pkB;yadirF_pkB;yadsruhT_pkB;yadsendeW_pkB
;syaD_09_morF_deteleD;syaD_09_oT_ypoC;syaD_51_oT_ypoC;ecnatsixE_eliF;BM_eziS_pkB;syaD09_noitneteR_pkB
Hist
;syaD_09_morF_deteleD;syaD_09_oT_ypoC;syaD_51_oT_ypoC;ecnatsixE_eliF;BM_eziS_pkB;syaD09_noitneteR_pkB;syaD51_noitneteR_pkB;emiT_hsiniF_pkB;emiT_tratS_pkB;emaN_eliF_pukcaB;emaN_BD;DIWOR::tsiH_pukcaB
DB_Shrink;DB_Backup;Copy_To_Tape;Backup_Status;Remarks;Manual_Comments;Ref_Rowid_Manual;Tape_Upd_Date;
*/
GO
No comments:
Post a Comment