DECLARE
@FileName VARCHAR(200),
@CurrentTime VARCHAR(50),
@DBName VARCHAR(100),
@SQL VARCHAR(1000),
@FilePath varchar (1000)
SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)
set @FilePath='D:\Desktop\DW\新建文件夹\KF2_'
DECLARE CurDBName CURSOR FOR
SELECT NAME FROM Master..SysDatabases where dbid>6
OPEN CurDBName
FETCH NEXT FROM CurDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
--Execute Backup
--SET @FileName = 'd:\db_bak\' + @DBName + '_' + @CurrentTime
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