1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
|
DECLARE @database_to_exclude VARCHAR(50);
SET @database_to_exclude = '';
DECLARE @database_name VARCHAR(50);
DECLARE @days_to_keep int;
DECLARE @path_of_backup_db varchar(300);
DECLARE @path_of_backup_log varchar(300);
DECLARE @path_of_forfiles varchar(100);
SET @days_to_keep = 3;
SET @path_of_backup_db = 'C:\temp';
SET @path_of_backup_log = 'C:\temp\log';
SET @path_of_forfiles = 'C:\temp\forfiles.exe';
DECLARE @time datetime;
SET @time = DATEADD(DAY, @days_to_keep, GETDATE());
DECLARE @version varchar(1);
SET @version = CONVERT(VARCHAR(1),SERVERPROPERTY('productversion'));
-- Name of instance
DECLARE @instance_name nvarchar(128);
SET @instance_name = @@ServerName;
-- Backup path
DECLARE @backup_path_db varchar(400);
DECLARE @backup_path_log varchar(400);
DECLARE @cmd varchar(800);
DECLARE @varcharDaysToKeep VARCHAR(3);
SET @varcharDaysToKeep = CONVERT(VARCHAR(3),@days_to_keep);
SET @path_of_backup_db = @path_of_backup_db;
SET @path_of_backup_log = @path_of_backup_log;
SET @database_name = 'model'
BEGIN TRY
SET @backup_path_log = @path_of_backup_log + '\' + REPLACE(@instance_name,'\', '') + '\' + REPLACE(@database_name, ' ', '_');
PRINT 'Nettoyer répertoire log : ' + @backup_path_log;
SET @cmd = @path_of_forfiles + ' -p' + @backup_path_log + '\ -m*.7z -c"cmd /c echo @PATH\@FILE est un fichier de backup log 7z vieux de plus de ' + @varcharDaysToKeep + ' jours" -d' + @varcharDaysToKeep + ' >> ' + @backup_path_log + '\log_del.txt';
EXEC xp_cmdshell @cmd;
SET @cmd = @path_of_forfiles + ' -p' + @backup_path_log + '\ -m*.7z -c"cmd /c del @PATH\@FILE" -d' + @varcharDaysToKeep;
EXEC xp_cmdshell @cmd;
EXECUTE master.dbo.xp_delete_file 0, @backup_path_log, N'trn', @time, 1;
PRINT @backup_path_log
END TRY
BEGIN CATCH
PRINT ' Entering CATCH block.';
PRINT ' File not found';
PRINT ' Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
PRINT ' Error Message : ' + ERROR_MESSAGE();
PRINT ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
PRINT ' Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10));
PRINT ' Error Line : ' + CAST(ERROR_LINE() AS VARCHAR(10));
PRINT ' Error Proc : ' + ISNULL(ERROR_PROCEDURE(), 'Not within proc');
PRINT ' Exiting CATCH block.';
END CATCH
GO |
Partager