Click Manage/SQL server proxy/job
Create a job:
On the General tab, enter "name" in "regular backup database", "enable", select "check", and "target local server ",
"Category" select "database service ",
On the "Steps" tab, create a step. Step name: backup database; Type: Transact-SQL script (TSQL );
Database: the database to be backed up
Command (multiple databases can be backed up at the same time ):
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-BACKUP ', 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-BACKUP ', NOSKIP, STATS = 10, NOFORMAT
Back up all user Databases
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