sql 自動備份的存儲過程

doctorzhou 2010-09-20

展開全文

create proc bak
as
-- 用腳本處理的示例
-- 假設(shè)一天一個完全備份
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,                -- 備份文件保留的天數(shù)

    @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