SQLSERVER 数据库备份脚本-支持多库备份

原文:SQLSERVER 数据库备份脚本-支持多库备份

<pre name="code" class="sql">--变量定义

DECLARE @backupfile VARCHAR(1024)

DECLARE @backdesc VARCHAR(1024)

DECLARE @filename VARCHAR(1024)

DECLARE @path VARCHAR(1024)

DECLARE @dbname VARCHAR(1024)

DECLARE @extension_name VARCHAR(16)

--备份参数

DECLARE tmp_Cur CURSOR

FOR

    SELECT  name

    FROM    [sys].[databases]

    WHERE   LOWER(name) IN ( 'db1', 'db2' )

SET @path = N'D:\Backup\Autoback\';

SET @extension_name = N'bak';

--生成文件名

SET @filename = CONVERT(VARCHAR(1024), GETDATE(), 120)

SET @filename = REPLACE(@filename, ':', '')

SET @filename = REPLACE(@filename, '-', '')

SET @filename = REPLACE(@filename, ' ', '')

SET @filename = @filename + '_' + CONVERT (VARCHAR(3), DATEPART(ms, GETDATE()))

    + N'.' + @extension_name

OPEN tmp_Cur;

FETCH NEXT FROM tmp_Cur INTO @dbname;

WHILE @@FETCH_STATUS = 0

    BEGIN

		-- 得到完整目标文件，数据库将备份到这个文件中

        SET @backupfile = @path + @dbname + @filename

        --SELECT  @backupfile

		SET @backdesc =@dbname + N'-完整 数据库 备份'

		-- 开始备份, COMPRESSION 参数表示压缩，可节省磁盘空间

        BACKUP DATABASE @dbname TO DISK = @backupfile WITH NOFORMAT, NOINIT,  NAME = @backdesc, SKIP, NOREWIND, NOUNLOAD,  STATS = 10, COMPRESSION

        FETCH NEXT FROM tmp_Cur INTO @dbname

    END

CLOSE tmp_Cur;

DEALLOCATE tmp_Cur;

-- 删除3天前的备份文件

DECLARE @olddate DATETIME

SELECT  @olddate = DATEADD(d, -3, GETDATE())

-- 执行删除 (SQL 2008 具备)

EXECUTE master.dbo.xp_delete_file 0, @path, @extension_name, @olddate, 1