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:BackupAutoback';
SET @extension_name = N'bak';

--生成文件名
SET @filename = CONVERT(VARCHAR(1024),GETDATE(),120)
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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!