在SQL SERVER中，添加JOB，可以使用以下语句设置定期备份数据库。

DECLARE @name VARCHAR(50)--databasename

DECLARE @path VARCHAR(256)--pathforbackupfiles

DECLARE @fileName VARCHAR(256)--filenameforbackup

DECLARE @fileDate VARCHAR(20)--usedforfilename SET @path='E:\Backup' SELECT @fileDate=CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR  SELECT [name]  FROM master.dbo.sysdatabases  WHERE [name] NOT IN('master','model','msdb','tempdb')  

OPEN db_cursor  FETCH NEXT FROM db_cursor INTO @name  WHILE @@FETCH_STATUS=0  

BEGIN   

SET @fileName=@path+@name+'_'+@fileDate+'.BAK'  

BACKUP DATABASE @name TO DISK=@fileName  

FETCH NEXT FROM db_cursor INTO @name  

END  CLOSE db_cursor  

DEALLOCATE db_cursor

-----------------------

DECLARE @name VARCHAR(50)--databasename

DECLARE @path VARCHAR(256)--pathforbackupfiles

DECLARE @fileName VARCHAR(256)--filenameforbackup

DECLARE @fileDate VARCHAR(20)--usedforfilename SET @name='dbname' SET @path='E:\' SELECT @fileDate=CONVERT(VARCHAR(20),GETDATE(),112) SET @fileName=@path+@name+'_'+@fileDate+'.BAK' BACKUP DATABASE @name TO DISK=@fileName ------------------------

posted on 2014-07-12 11:40  Carlwave-陆飞(Fei.Lu)  阅读(431)  评论()    收藏  举报