1 --批量备份数据库201911 2 --开启文件夹权限 3 GO 4 SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1 5 RECONFIGURE 6 GO 7 SP_CONFIGURE 'XP_CMDSHELL',1 8 RECONFIGURE 9 GO 10 11 DECLARE 12 @FileName VARCHAR(200), 13 @CurrentTime VARCHAR(50), 14 @DBName VARCHAR(100), 15 @SQL VARCHAR(1000), 16 @FilePath VARCHAR(100) 17 18 --SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR) 19 --年月日 20 SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) 21 22 SET @FilePath = 'E:DBBak' + @CurrentTime + '' 23 --select CONVERT(CHAR(8),GETDATE(),112) 24 --文件夹不存在，则创建 25 declare @TEMP TABLE(A INT,B INT,C INT)--建立虚拟表，用来判断文件夹是否存在 26 INSERT @TEMP EXEC [MASTER]..XP_FILEEXIST @FilePath 27 IF NOT EXISTS(SELECT * FROM @TEMP WHERE B=1) 28 BEGIN 29 --XP_CMDSHELL不允许使用变量拼接，所以使用exec方法 30 declare @EX NVARCHAR(255) 31 SET @EX = 'EXEC XP_CMDSHELL ''MKDIR ' + @FilePath + ''''; 32 EXEC(@EX) 33 END 34 35 36 37 --获取所有非系统数据库 38 DECLARE CurDBName CURSOR FOR 39 SELECT NAME FROM Master..SysDatabases where dbid>4 40 41 --循环备份数据库 42 OPEN CurDBName 43 FETCH NEXT FROM CurDBName INTO @DBName 44 WHILE @@FETCH_STATUS = 0 45 BEGIN 46 --Execute Backup 47 SET @FileName = @FilePath + @DBName + '_' + @CurrentTime 48 SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' + 49 ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT' 50 EXEC(@SQL) 51 52 --Get Next DataBase 53 FETCH NEXT FROM CurDBName INTO @DBName 54 END 55 56 CLOSE CurDBName 57 DEALLOCATE CurDBName 58 59 60 /* 61 --删除30天之前的备份文件 62 --开启文件夹权限 63 GO 64 SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1 65 RECONFIGURE 66 GO 67 SP_CONFIGURE 'XP_CMDSHELL',1 68 RECONFIGURE 69 GO 70 EXEC xp_cmdshell 'forfiles /p F:DBBak /m * -d -30 /c "cmd /c rd /s /q @path"' 71 */