分享:sp_backupdb数据库备份方案(二)
TDATE()) AS VARCHAR),2) + '.diffbak'' WITH DIFFERENTIAL '
PRINT @sql_diff_backup
EXEC (@sql_diff_backup)
SET @cmd_del_backup_files_diff = ' dir ' + @db_dir + ' /b/a '
PRINT @cmd_del_backup_files_diff
CREATE TABLE #backup_diff_files ( files VARCHAR(512))
INSERT INTO #backup_diff_files ( files )
EXEC xp_cmdshell @cmd_del_backup_files_diff
DECLARE c_del_files_full_backup CURSOR FAST_FORWARD FOR
SELECT files--,CAST(SUBSTRING(files,LEN(files)- 14,8) AS DATETIME) dt
FROM #backup_diff_files
WHERE files LIKE '%.diffbak'
AND CASE WHEN ISNUMERIC(SUBSTRING(files,LEN(files)- 14,8)) = 1 THEN CAST(SUBSTRING(files,LEN(files)- 16,8) AS DATETIME) ELSE '1901-01-01' END < DATEADD(DAY,-1*ABS(@del_days_ago),GETDATE())
OPEN c_del_files_full_backup
FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files_diff
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd_del_backup_files_diff = 'del ' + @db_dir + '\' + @cmd_del_backup_files_diff
PRINT @cmd_del_backup_files_diff
EXEC xp_cmdshell @cmd_del_backup_files_diff --执行删除CMD命令
FETCH NEXT FROM c_del_files_full_backup INTO @cmd_del_backup_files_diff
END
CLOSE c_del_files_full_backup --关闭游标
DEALLOCATE c_del_files_full_backup --销毁游标
DROP TABLE #backup_diff_files
END
FETCH NEXT FROM c INTO @db
END
CLOSE c --关闭游标
DEALLOCATE c --销毁游标
go