备份删除

SQL操作与文件管理结合的高效备份策略

最新推荐文章于 2024-09-02 17:26:08 发布

转载最新推荐文章于 2024-09-02 17:26:08 发布 · 84 阅读

本文介绍了一种使用SQL语句与Windows命令行执行高效备份的方法，包括创建临时表、筛选特定文件、执行复制和删除操作，特别关注了备份文件的日期筛选、文件类型筛选以及路径转换。

CREATE TABLE #files
(
id INT IDENTITY(1, 1) ,
name VARCHAR(500)
)
DECLARE @dir VARCHAR(600)= 'E:\DataBaseBackup\'

DECLARE @dir1 VARCHAR(600)= 'dir ' + @dir

DECLARE @day INT= 8
--大于等于8天的备份

INSERT #files ( name )
EXEC xp_cmdshell @dir1;
WITH cte
AS ( SELECT name = @dir + RTRIM(LTRIM(REVERSE(LEFT(REVERSE(name),
CHARINDEX(' ',
REVERSE(name))))))
FROM #files
WHERE id > 5
AND name NOT LIKE '%DIR%'
AND name NOT LIKE '%字节%'
)
SELECT name
INTO #t1
FROM ( SELECT CASE WHEN id >= @day THEN '11'
ELSE NULL
END id, name
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY name DESC ) id,
*
FROM cte S0
WHERE S0.name LIKE '%SGPZ%'
UNION ALL
SELECT ROW_NUMBER() OVER ( ORDER BY name DESC ) id,
*
FROM cte S0
WHERE S0.name LIKE '%SCT%'
) s1
) s2
WHERE s2.id IS NOT NULL

DECLARE @ACTION1 VARCHAR(MAX)

DECLARE pcurr CURSOR
FOR
SELECT name
FROM #t1

OPEN pcurr
FETCH NEXT FROM pcurr INTO @ACTION1
WHILE ( @@FETCH_STATUS = 0 )
BEGIN

DECLARE @cmd VARCHAR(4000)= ''

SET @cmd = 'COPY ' + @ACTION1
+ CASE WHEN @ACTION1 LIKE '%SGPZ%'
THEN ' ' + REPLACE(@ACTION1, 'E:\DataBaseBackup\',
'd:\DataBaseBackup\SGPZ\')
ELSE ' ' + REPLACE(@ACTION1, 'E:\DataBaseBackup\',
'd:\DataBaseBackup\SCT\')
END
PRINT @cmd

EXEC xp_cmdshell @cmd --拷贝

SET @cmd = 'del ' + @ACTION1

PRINT @cmd

EXEC xp_cmdshell @cmd --删除

----------------------------
FETCH NEXT FROM pcurr INTO @ACTION1
END
CLOSE pcurr
DEALLOCATE pcurr

DROP TABLE #files
DROP TABLE #t1