Backup database job with SQL Server Agent

11 April,2013 by Jack Vamvas

Sometimes I receive a request for instructions on how to set up a SQL Server Agent Job  to backup a database.

Setting up a SQL Server Agent Job  is easy with the GUI. Follow these instructions to set up a basic SQL Server Agent Job.

 Once you’ve set up this job to manage a backup , you can schedule a job or add custom error handling , for example, sending an email on success or failure or SQL Server - BackUp All Databases 

 Creating a backup can cause a high level of IO . Consider the impact of creating a backup on a server read more on BACKUPIO and sql database backup

Create a SQL Server Agent Job

Open SQL Server Management Studio (SSMS ) and connect to the SQL Server Instance. Expand the SQL Server Agent node and right click on Jobs , and click on New Job

Agent_new_job_1

Type the name

Type a name for the SQL Server Agent Job. Also make sure the owner is OK and has sufficient permissions

Click on Steps

Agent_type_name_2

Create the job step

Click on the New button

Agent_new_step_3

Add the code

 

Add a Step name and then add the code. The code in this example is to backup a database to disk . Use the script on SQL Server - BackUp All Databases to backup all databases. Customise the code for your requirements.

 Click OK

Agent_job_step_4

Click OK to create this job

 Agent_job_ok_5

Start the job

Expand the SQL Server Agent Job , right click on your job and click the Start Job option

Agent_start_job_6

Process Dialog

 Once you’ve started the job , a Process dialog will appear , reporting on in- progress job status

Agent_progress_7

Check View History

 Expand the Jobs node , right click on the Job and View History.

 Agent_view_history_8

 

Read More

 

SQL Server - Display sql backup history for a single database

BACKUP LOG with TRUNCATE_ONLY is discontinued

Slow SQL Server Backup and Restore with DBCC TRACEON (3004, 3605, -1)

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Backup database job with SQL Server Agent


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer