点击 管理／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