sql 自动备份的存储过程

create proc bak
as
-- 用脚本处理的示例
-- 假设一天一个完全备份
DECLARE
    @database_backup sysname,
    @path_backup nvarchar(260),
    @keep_days int,
    @file_backup nvarchar(260),
    @file_delete nvarchar(260);

SELECT
    @database_backup = N'card_his',  -- 要备份的库名
    @path_backup = N'd:\backup\',  -- 备份目录
    @keep_days = 7,                -- 备份文件保留的天数
   
    @database_backup = PARSENAME(@database_backup, 1),
    @path_backup = CASE
                        WHEN @path_backup LIKE N'%\' THEN @path_backup
                        ELSE @path_backup + N'\'
                    END,
    @file_backup = @path_backup
                + @database_backup
                + N'_' + CONVERT(char(10), GETDATE(), 112)
                + N'.bak',
    @file_delete = @path_backup
                + @database_backup
                + N'_' + CONVERT(char(10), GETDATE() - @keep_days, 112)
                + N'.bak';

-- 备份
DECLARE
    @s nvarchar(4000);
SET @s = N'
BACKUP DATABASE ' + QUOTENAME(@database_backup) + N'
    TO DISK = @file_backup
';
EXEC sp_executesql
    @s,
    N'
        @file_backup nvarchar(260)
    ',
    @file_backup;
   
-- 删除历史
DECLARE
    @file_exist int;
EXEC master.dbo.xp_fileexist
    @file_delete,
    @file_exist OUT;

IF @file_exist = 1
BEGIN
    SET @s = N'DEL "' + @file_delete + N'"';
    EXEC master.dbo.xp_cmdshell @s;
END