SQL 建立数据库备份作业

declare @name nvarchar(50)
declare @datetime char(14)
declare @path nvarchar(255)
declare @bakfile nvarchar(255)
set @name='DBNAME'
set @datetime=CONVERT(char(8),GETDATE(),112)+REPLACE(CONVERT(char(8),getdate(),108),':','')
set @path='D:\DBDataBackup'
set @bakfile=@path+''+'Bak_'+@datetime+'_'+@name+'.bak'
select @bakfile
backup database @name to disk=@bakfile with name=@name,checksum

 

所有

DECLARE
      @FileName VARCHAR(200),
      @CurrentTime VARCHAR(50),
      @DBName VARCHAR(100),
      @SQL VARCHAR(1000),
      @FilePath VARCHAR(100),
      @DelFilePath VARCHAR(100)
 
--SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)
--年月日
SET @FilePath = 'D:\DB_Backup\' 
SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112)
--获取所有非系统数据库
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
    --备份数据数据
    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)
    --删除7天以前的数据
    SET @DelFilePath='del '+@FilePath + @DBName + '_' + CONVERT(CHAR(8),GETDATE()-30,112)+'.bak'
    SET @SQL = 'EXEC master..xp_cmdshell ''' + @DelFilePath + '''';
    
     EXEC(@SQL)
    --Get Next DataBase
    FETCH NEXT FROM CurDBName INTO @DBName
END
 
CLOSE CurDBName
DEALLOCATE CurDBName