Knowledge points for SQL Agent jobs

First, the basic concept

SQL Server Agent is a task planner and alert manager that, in real-world applications and environments, you can define periodic activities as a single task and have them run automatically with the help of SQL Server Agent.

The focus of SQL Server Agent is planning (Schedule) and Job (job), we create the job, and then add the job to a certain plan, let the agent help us to run it automatically. In short, for example, we need to implement the automatic database backup function. We create a new job that backs up the database, and then add the job to a plan that is executed every night 11 o'clock, and finally put the plan into an agent.

Second, create the job and add the job to the schedule:

1. In Object Explorer, connect to an instance of the SQL Server database engine, and then expand the instance.
2. Expand SQL Server Agent, expand Jobs, right-click the job you want to schedule, and click New Job.
3. Enter job Name: Job-backupdatabase
4. In the Step Setps box, click Insert, and fill in the Step name: Step-backdatabase. Type selection: TSQL, the script looks like this:

DECLARE @strPath NVARCHAR ( $) Set@strPath = Convert (NVARCHAR ( +), GETDATE (), -) Set@strPath = REPLACE (@strPath,':','.') Set@strPath ='D:\bak\ ' + @strPath +'. bak'BACKUP DATABASE [TSQL2012] to DISK= @strPath with Noinit, Nounload, noskip, STATS =Ten, Noformat

6. This completes the creation of the job.
7. Note: The D:\bak\ here is my path name and can be customized. There is the path must be created in advance, or the execution of the job will be error. The database we need to back up here is TSQL2012 and can be changed according to the database that you need to back up.
8. Right-click on the job Job-backupdatabase, select "Properties", in the "Plan Schedules" tab, tap "New new", fill in the plan name: Schedule-backupdatabase, for "schedule type", select the plan that will be executed repeatedly, and complete the "frequency", "Daily Frequency", and "duration" on the dialog box.
9. When you do this, it's the equivalent of creating a new plan: Schedule-backupdatabase, and attaching the job: Job-backupdatabase to the plan.

If you want to test whether the job is performing properly, you can right-click the job and select Start job at step. Of course, the agent can also be used to test the success.

Knowledge points for SQL Agent jobs