使用作业备份单个或所有sqlserver数据库

点击 管理／sql server代理／作业
新建作业：
常规选项卡里,"名称"填写"定时备份数据库"，"启用"前面选"对勾"，"以本地服务器为目标"，
 "分类"选择"数据库服务"，
步骤选项卡里，新建步骤，步骤名：备份数据库；类型：Transact-SQL脚本(TSQL);

 数据库：要备份的数据库
 命令（可同时备份多个数据库）：

DECLARE
 @FileName VARCHAR(200),
 @CurrentTime VARCHAR(50)

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)

SET @FileName = 'D:\CE_BPS\DataBaseBackup\DPC_TEXT' + @CurrentTime
BACKUP DATABASE [CE_BPS_DPC_TEXT] TO DISK = @FileName WITH NOINIT, NOUNLOAD, NAME = N'CE_BPS_DPC_TEXT-备份', NOSKIP, STATS = 10, NOFORMAT

SET @FileName = 'D:\CE_BPS\DataBaseBackup\DPC_IMAGE' + @CurrentTime
BACKUP DATABASE [CE_BPS_DPC_IMAGE] TO DISK = @FileName WITH NOINIT, NOUNLOAD, NAME = N'CE_BPS_DPC_IMAGE-备份', NOSKIP, STATS = 10,NOFORMAT

 

备份所有用户数据库

 
 1 DECLARE
 2     @FileName VARCHAR(200),
 3     @CurrentTime VARCHAR(50),
 4     @DBName VARCHAR(100),
 5     @SQL VARCHAR(1000)
 6
 7 SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112) + CAST(DATEPART(hh, GETDATE()) AS VARCHAR) + CAST(DATEPART(mi, GETDATE()) AS VARCHAR)
 8
 9 DECLARE CurDBName CURSOR FOR SELECT NAME FROM Master..SysDatabases where dbid>4
10 OPEN CurDBName
11     FETCH NEXT FROM CurDBName INTO @DBName
12
13     WHILE @@FETCH_STATUS = 0
14     BEGIN    
15         --Execute Backup
16         SET @FileName = 'D:\backup\' + @DBName + @CurrentTime
17         SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName +
18             ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, NOFORMAT'
19         EXEC(@SQL)
20
21         --Get Next DataBase
22         FETCH NEXT FROM CurDBName INTO @DBName
23     END
24 CLOSE CurDBName
25 DEALLOCATE CurDBName
26