Backup database – SQL Server Agent Job

The backup is not important…until you need it. It is very very important to backup your database. And you can schedule a job that will backup your database for you, and the creation is very simple. I have included for you the steps to create the job. The backup file that is created will have the name of database and current date. So it will be simple for you to know when was made the backup of the database. This is the simplest version of the backup job because you can configure it to send notification on failure etc. but I wanted it to be the simplest job possible.

I hope it is easy for you to follow these steps. If you have trouble implementing it, just leave a comment saying that you cannot follow the steps and I can send you an email with the script, you will just have to execute it to create the job.

First you have to make sure SQL Server Agent service is running.

After that just follow these steps:

1. You go to SSMS and expand the  SQL Server Agent.

2. Right Click on Jobs and choose New Job…

3. Add a name for the job, for example BackupDatabaseJob

4. On Select a page pane select Step and click New…

5. When New Job Step window opens choose a name for the job step, for example BackupDatabaseStep, leave the Type: Transact-SQL script(T-sql)

6. In the command pane write the following code (you have to change appropriate name of the database and appropriate path for backup) and then click OK:

declare @path nvarchar(max)
set @path = ‘D:\adventureworks_’ + convert(nvarchar(50), getdate(), 104) + ‘.bak’
backup database adventureworks to disk=@path

7. In the New Job window  On Select a page pane click Schedules and then click New…

8. On New Job Schedule window choose a name for a schedule, for example Weekly Schedule, choose Frequency and Daily Frequency and click OK.

9. On New Job window click OK.

You have created a new SQL Server Agent Job that runs weekly on schedule.

Have a nice day!


This post is written by Donika Bajrami. You can visit her personal blog here.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at

Up ↑

%d bloggers like this: