Jobs In SQL Server

Hello readers, in my previous article we installed default and named instances of SQL Server.

In this article, we’ll learn about Jobs in SQL Server. We’ll also see how we can setup a simple backup job with help of Maintenance Plan.

Let’s begin with some introduction.

SQL Job

A Job in SQL Server is a container that allows packaging one or more steps in process that need to be scheduled and executed. We can say, it a series of query actions that SQL Server performs.

Create a SQL Job

To create a job in SQL Server, a user must be a member of SQL Server Agent fixed database roles or he/she must be a sysadmin because only member of these roles can create or modify the jobs.

For example, If you see our user/login (i.e. csharpcorner), it has only access to “CSHarpCornerDB” and when we connect with that login, SQL Server Agent gets disappeared as in the following.

This is because our login is not a member of sysadmin or SQL Server Agent fixed database role.

These roles are stored under msdb database which give administrator control over access to SQL Server agent.

The following are the 3 SQL Server Agent roles.

SQLAgentUserRole
SQlAgentReaderRole
SQLAgentOperatorRole

We’ll add our user to these roles. To perform this action (via Administrator mode) open Security, select Login, User Mapping and then select MSDB database. After that give access to above SQL Agent roles as in the following screenshot:

msdb

After giving those permissions, when you reconnect SQL Server, you’ll see SQL Server Agent is now visible.

We’re done with configuration part; now let’s move towards example where we’ll automate our backup for databases via SQL Server job.

Setup a Job

Follow below steps to setup a job.

Right click on SQL Server Agent, New, Job, and then Enter Job Name, Category, Description. Go to Steps tab, click New, enter step name, select Type of Step, select Database and then click OK.

You can also schedule your backup by selecting Schedule tab.

Important point over here is while creating Job step, you’ll see Type, and this defines the type of steps which can be created. These job steps can be created in various ways like listed below:

T-SQL
SSIS Packages
ActiveX scripts
SSAS commands
SSAS Queries
Replication Snapshot, etc.

When you’ll expand the Type category, you’ll find various other types as shown below:

After performing all above steps, you’ll get something like the following:

new job

general

Now click Ok and you’re done. Open Job Activity Monitor and you’ll see your created job over there.

click on Ok

We’ve setup our backup job manually by performing above steps. These steps seems little bit longer, we can create this backup job even with the help of Maintenance Plan in easy steps. Let’s see that as well.

A Maintenance Plan is a set of measures taken to ensure that a database is properly maintained and routine backups are scheduled and handled.

Its editor somewhat looks like Visual Studio editor where we drag and drop controls in our form.

Maintenance plan in SQL Server includes various tasks such as:

Backup Database Task
Cleanup Task
Database Shrinking Task
Update Statistics Task
Index Rebuild, etc.

In this article we’ll see Backup task.

To perform this, follow below steps (performing in Admin mode).

Expand Management, then right click on Maintenance Plan, then click New Maintenance Plan as shown below:

After that a window will popup asking Name for maintenance plan.

Give a suitable name and click OK. As you’ll click on OK, it’ll redirect you to Design mode and you’ll see some tools in toolbox window.

toolbox

Here, we’ll create our backup job. We can create job not only for 1 database but also for other databases in one click such as performing all system databases backup or all user databases backup, etc.

Let’s begin with our example where we’ll create a backup plan and we’ll also clean-up backup files older than 1 week.

From Toolbox, drag and drop “Back Up Database Task” as shown below.

Right click on this control and select Edit and you’ll get “Backup Database Task” window where you select what type of backup you want to perform on databases, which database to be added in maintenance plan, location of the backup, auto-create directory for each database. The following is the screenshot,

In this window you’ll see, there’s a dropdown to select databases. When you expand the drop-down you’ll see something like the following screenshot:

On this window, you’ll find the following four options i.e.

All Databases: Choosing this option will backup all databases, user and system databases.
System Databases: Choosing this option will backup only system databases.
All User databases (excluding master, model, msdb, tempdb): Choosing this option will backup all the databases of users.
These databases: Here you can select the databases for which you want to perform backup. I’ve selected “CSharpCornerDB” and “DB1”.

If you tick the option “Ignore databases where the state is not online”, job will ignore the databases which are OFFLINE.

As you select your databases and click on OK, immediately it change from “<Select One or more>” to “Specific databases”.

If you want to view the T-SQL code just click on button “View T-SQL” and you’ll get T-SQL code as in the following screenshot:

Now click on OK and your backup task is ready.

backup

If you see, red crossed sign now disappeared for the control.

Now, we’ll setup clean-up task which will delete backups older than 1 week.

Drag and drop “History Cleanup Task” which looks like the following image.

Right click on this control and select “Edit” and you’ll get a window to remove historical data. I’m selecting 1 Week, you can also select “Days”, “Hours”, “Months”, and “Year”. Once you’re done with that, you’ll get something like the following:

We’re done with both the task. Now we need to decide the order of execution i.e. how we want to run this plan. Here we’ve the following 2 options:

Clean older backup and then take fresh backup.
Take fresh backup and then clean older backup.

I’ll recommend going with the 2nd option, first taking backup and then cleaning the older one. This is because, if you select 1st and will perform clean-up first and due to some network issue if your backup fails, you’ll lose you previous backup also.

If you’re going with 2nd option, just pull the green arrow of “Backup Database Task” to “History Cleanup Task” as shown below:

If you’re going with 1st option, then connect arrow from “History Cleanup Task” to “Backup Database Task”.

Save your maintenance plan. When you see the Maintenance Plans folder you’ll find your plan over there.

Maintenance Plans folder

Go to Job Activity Monitor or expand Jobs folder in SQL Server Agent and you’ll see that SQL Server automatically created a backup job for you. Isn’t it’s good?

Jobs

Now you just have to schedule this job. You can also schedule it while creating maintenance plan. Just click on the schedule button and put some schedule. You’re done!

Now we’ll run this job and check how and where our backup files get stored.

Start the job and watch the progress.

Our job completed successfully and let’s check the files.

When you’ll see the backup location, you’ll find that SQL Server automatically created folders for each databases and stored the backup files in that.

The following is the backup file stored in CSharpCornerDB folder.

Conclusion

In this article we learned what SQL Server Job is and the ways by which we can create Job steps. We also created a simple backup job with help of GUI and afterwards we’ve seen what maintenance plan is and we’ve also created a backup job via Maintenance Plan.

In our next article we’ll learn some other concepts of SQL Server, till then keep learning and sharing.

Please provide your valuable feedback and comment which encourages me to provide better article next time.