SQL批量异地备份、删除过期备份

-- =============================================
-- Author:      <草根IT工作室>
-- Blog:        <http://www.it10000.org/>
-- Create date: <2014/11/12>
-- Description: <批量备份数据库>
-- =============================================
--确保正写入异地文件夹，先断开X盘符映射
exec master..xp_cmdshell 'net use x: /delete'
--映射共享为X盘
exec master..xp_cmdshell 'net use x: \\XXX\XXX$ "密码" /user:IP\用户名'
--删除过期备份、删除7日前备份（参数/d-7中的数字为可根据实际情况更改）
EXEC master..xp_cmdshell 'forfiles /p x:\ /s /m *.bak /d -7 /c "CMD /C del /Q /F @FILE"'
--开始批量备份数据库
DECLARE
      @FileName VARCHAR(200),
      @CurrentTime VARCHAR(50),
      @DBName VARCHAR(100),
      @SQL VARCHAR(1000)
SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

DECLARE CurDBName CURSOR FOR
--备份除系统外的所有数据库（注意根据SQL版本选择dbid值）
    SELECT NAME FROM Master..SysDatabases where dbid>4

OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Execute Backup
    SET @FileName = 'x:\' + @DBName + '_' + @CurrentTime
    SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
    EXEC(@SQL)

    --Get Next DataBase
    FETCH NEXT FROM CurDBName INTO @DBName
END

CLOSE CurDBName
DEALLOCATE CurDBName
--完事后断开映射
exec master..xp_cmdshell 'net use x: /delete'

未经允许不得转载：运维大虾 » SQL批量异地备份、删除过期备份