很多时候,我们都需要将数据库进行备份,当服务器上数据库较多时,不可能一个数据库创建一个定时任务进行备份,这时,就需要进行批量的数据库备份操作,好了,废话不多说,具体实现语句如下:
--开启文件夹权限
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE
GO
SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE
GO
DECLARE
@FileName VARCHAR(200),
@CurrentTime VARCHAR(50),
@DBName VARCHAR(100),
@SQL VARCHAR(1000),
@FilePath VARCHAR(100)
--SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)
--年月日
SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112)
SET @FilePath = 'F:\DB_Daily_backup\' + @CurrentTime + '\'
--select CONVERT(CHAR(8),GETDATE(),112)
--文件夹不存在,则创建
declare @TEMP TABLE(A INT,B INT,C INT)--建立虚拟表,用来判断文件夹是否存在
INSERT @TEMP EXEC [MASTER]..XP_FILEEXIST @FilePath
IF NOT EXISTS(SELECT * FROM @TEMP WHERE B=1)
BEGIN
--XP_CMDSHELL不允许使用变量拼接,所以使用exec方法
declare @EX NVARCHAR(255)
SET @EX = 'EXEC XP_CMDSHELL ''MKDIR ' + @FilePath + '''';
EXEC(@EX)
END
--获取所有非系统数据库
DECLARE CurDBName CURSOR FOR
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 = @FilePath + @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
在SQL代理中,建立定时作业,按指定时间定时执行该脚本即可。