SQL SERVER 批量备份所有数据库

USE [master]
DECLARE @backupfile NVARCHAR(1024)  
DECLARE @backdesc NVARCHAR(1024)  
DECLARE @filename NVARCHAR(1024)  
DECLARE @path NVARCHAR(1024)  
DECLARE @dbname SYSNAME
DECLARE @extension_name NVARCHAR(50)
SET @path = N'D:\DBBackup\';   -- 此处是服务器的备份文件输出路径
SET @extension_name = N'.bak';
IF (OBJECT_ID('tempdb..#Temp') IS NOT NULL)  
  DROP TABLE #Temp  
CREATE TABLE #Temp (id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@path,3)
INSERT #Temp EXEC master..xp_fileexist @fpath
INSERT #Temp EXEC master..xp_fileexist @path
--如果指定盘符有误不存在，则返回错误提示：
IF EXISTS(SELECT 1 FROM #Temp WHERE id=1 AND c=0)
BEGIN
    PRINT CHAR(10) + N'输入的盘符不存在，请重新输入!'
	----临时表删除--------
IF (OBJECT_ID('tempdb..#Temp') IS NOT NULL)  
BEGIN  
  DROP TABLE #Temp  
END
RETURN
END
--如果不存在指定的文件夹，则创建:
ELSE IF EXISTS(SELECT 1 FROM #Temp WHERE b=0 AND id=2)
BEGIN
PRINT CHAR(10) + N'正在创建目录'
EXEC sp_configure 'show advanced options',1
reconfigure
EXEC sp_configure 'xp_cmdshell',1
reconfigure
    DECLARE @mddir NVARCHAR(100)
    SET @mddir='md '+@path
    EXEC master..xp_cmdshell @mddir
	PRINT CHAR(10) + N'创建目录完成'
END
----临时表删除--------
IF (OBJECT_ID('tempdb..#Temp') IS NOT NULL)  
BEGIN  
  DROP TABLE #Temp  
END
--备份
PRINT CHAR(10) + N'开始备份所有数据库'
DECLARE tmp_Cur CURSOR  
FOR  
    SELECT  name  
    FROM    sys.databases  
    WHERE ([name] LIKE '%MGMT' )  -- 此处条件可以自定义，
    ORDER BY Name
 
OPEN tmp_Cur
FETCH NEXT FROM tmp_Cur INTO @dbname;  
WHILE @@FETCH_STATUS = 0    
    BEGIN  
        -- 得到完整目标文件，数据库将备份到这个文件中  
        SET @backupfile = @path + @dbname + @extension_name
        --SELECT  @backupfile  
        SET @backdesc =@dbname + N'-完整 数据库 备份'  
 
        -- 开始备份, COMPRESSION 参数表示压缩，可节省磁盘空间
  PRINT CHAR(10) + N'开始备份 '+@dbname
  PRINT CHAR(10)
        BACKUP DATABASE @dbname TO DISK = @backupfile WITH NOFORMAT, NOINIT,  NAME = @backdesc, SKIP, NOREWIND, NOUNLOAD,  STATS = 10, COMPRESSION  
        PRINT CHAR(10) + @dbname + N' 备份完成 '
        FETCH NEXT FROM tmp_Cur INTO @dbname  
    END  
CLOSE tmp_Cur
DEALLOCATE tmp_Cur
PRINT CHAR(10) + N'所有数据库备份完成'