كود عمل نسخة يومية من قاعدة البيانات بتاريخ مع حذف النسخة السابقة من 30 يوم مضي

ALTER PROCEDURE [dbo].[spBackUpDB]
AS
BEGIN
declare @sDataBaseName nvarchar(20)='Families'
declare @sPathMain nvarchar(200)=N'\\10.240.24.45\d$\BackUpDBs\families\backup\'

declare @sDirYear nvarchar(4)=CONVERT(nvarchar(4),year(GETDATE()))
declare @sDirMonth nvarchar(2)=substring(CONVERT(nvarchar(8),GETDATE(),112) ,5,2)
declare @sDate nvarchar(6)=substring(CONVERT(nvarchar(8),GETDATE(),112) ,3,6)

declare @sPathMonth nvarchar(200)=@sPathMain+@sDataBaseName+@sDirYear+'\'+@sDirMonth

EXEC master.dbo.xp_create_subdir @sPathMonth

declare @sPathBackUp nvarchar(200)=@sPathMonth + '\'+@sDataBaseName+@sDate+'.bak'

BACKUP DATABASE [MSSFamiliesDB] TO  DISK = @sPathBackUp  WITH NOFORMAT, INIT,  NAME = N'MSSFamiliesDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  COMPRESSION, STATS = 10
------------------------------------------------------------------
--delete old BackUpDB befor 30 days	

declare @dDateBefor date=DATEADD(DAY ,-30,GETDATE())
set @sDirYear  =CONVERT(nvarchar(4),year(@dDateBefor))
set @sDirMonth =substring(CONVERT(nvarchar(8),@dDateBefor,112) ,5,2)
set @sDate =substring(CONVERT(nvarchar(8),@dDateBefor,112) ,3,6)

set @sPathMonth =@sPathMain+@sDataBaseName+@sDirYear+'\'+@sDirMonth

set @sPathBackUp =@sPathMonth + '\'+@sDataBaseName+@sDate+'.bak'
BEGIN TRY
	--Delete Backup Date File
	declare @sPathDelete nvarchar(200)=N'del ' + @sPathBackUp
	EXEC master.dbo.xp_cmdshell @sPathDelete
	------------------------------------------------------------------
	--get Backup File In Folder  
	declare  @CommandShell TABLE( sFileName VARCHAR(512))
	 
	SET @sPathDelete = 'DIR '+ @sPathMonth + ' /A-D /B'
	 
	-- MSSQL insert exec - insert table from stored procedure execution
	INSERT INTO @CommandShell
	EXEC MASTER..xp_cmdshell   @sPathDelete 

	declare @sSchemaFileName  nvarchar(50)=@sDataBaseName+substring(@sDirYear,3,2)+@sDirMonth +'[0-9][0-9].bak'
	------------------------------------------------------------------
	--Detele Folder if No Backup File In
	if ISNULL((SELECT ISNULL(count(*),0) FROM  @CommandShell WHERE  sFileName LIKE @sSchemaFileName),0)=0
	BEGIN
		set @sPathDelete =N'rd /s /q ' + @sPathMonth
		EXEC master.dbo.xp_cmdshell @sPathDelete
	end
end TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage
END CATCH
end

كود عمل نسخة يومية من قاعدة البيانات بتاريخ مع حذف النسخة السابقة من 30 يوم مضي

مواضيع مشابهة

عدد الزوار حالياً